Annual Running Totals - First Period Values

[ilovasen]

Hi

I have this problem:
I am extracting data from Cognos Controller. Cognos Controller stores data in accumulated numbers over the year. To find the period value (as opposed to the accumulated value) for a period I have first made a calculated column in my Fact-table that makes a transactions into USD values and multiplies with the owner-percentage to get our ProRata-value in USD. I have then made a DAX formulae that accumulates this into period values based on a Date field in a Calendar-table (only first of month dates over some years). I have then made a Dax formula that finds the perevious periods value. A third Dax fomulae takes this period's (month) value less previous periods value to get the month value (rather than the accumulated value). This works fine with any month except for the the first period (January) in every year) which takes the Jan value less the Dec-value of previous year.

Formula for Last Month (accumulated): =if (HASONEVALUE(dimMonths[Start]);if(VALUES(dimMonths[MonthOfYear])>1;CALCULATE(sum(factFin[AmountUSDProRata]); DATEADD(dimMonths[Start]; -1; Month))))
Formulae for This Month (period value - not Accumulated): =sumx(factFin;factFin[AmountUSDProRata])-[LastMonthAmountUSDProRata] - where the SumX formula find the accumulated sum for this month

I have tried using an if-statement to set the Period 1 to Blank() but this dows not either work due to some Row-complexity.

Can anyone please suggest a solution?

Brgds,
Inge

 

[scottsen]

Any chance of sharing out the workbook (dropbox, google drive, whatever) ?

 

[ilovasen]

Thanks for the reply, Scott. Unfortunately I cannot share my model as it has 12 years of financial data in it. Sorry.

 

[scottsen]

Understandable. Can you see if you can edit your first post to make the measures more readable? Bonus points if you use Dax Formatter by SQLBI

 

[ilovasen]

Hi again Scott

My Problem is that I have a P&L account excerpt with running totals. As P&L accounts starts at 0 the following year, to get the monthly values I cannot just take ThisMonth less PreviousMonth as in January this will leave me the small January number less the large December. For January I then have to select only the running total without using ThisMonth and not ThisMonth less PreviousMonth.

Year - Month - RunningTotal
1-1-1
1-2-2
1-3-3
1-4-4
1-5-5
1-6-6
1-7-7
1-8-8
1-9-9
1-10-10
1-11-11
1-12-12
2-1-1
2-2-2 and so on

The formula shall give the value 1 for Year 2-Month 1 - not the 12 I am getting.

The running total is a column of data. I want to make a Measure/Calculated Field that gets the monthly value not the RunningTotal - I can easily find that for month 2-12 by subtracting ThisMonth RunningTotal less PreviousMonth RunningTotal, but the problem is to get the Month 1 - January correct. I have tried with an IF formula to test if the month is January/1 but that gives an error message.

I feel very stupid as to this - I guess there is a very simple solution that I have not yet found.

Grateful for any help.

Brgds, Inge

 

[scottsen]

=CALCULATE(SUM(factFin[AmountUSDProRata]), FILTER(dimMonths, dimMonths[MonthId] <= MAX(dimMonths[MonthId])), VALUES(dimMonths[MonthOfYear]))

Without fulling understanding... the above feels correct to me. "Gimme the SUM for all rows less than or equal to the max of the current context, in the current year"

 

[ilovasen]

Dear Scott

Thanks for the effort - I really appreciate it!

This definitely helps as it limits the calculation to the present year. However it still aggregates the numbers to a running total and is still not yet able to reflect the individual months.

What I try to do is to reduce the Running Total (factFin[USDProRataAC]) into monthly numbers so that I can do calculations on the monthly transaction as I ideally I should have had the monthly transaction from the accounting system rather than the running totals in the firstplace (however they are only stored as running totals).

Columns: MonthOfYear, DateKey
Caclulated Columns: USDProRataAC
Others: are Calulated Fields/Measures

Sum of USDProRataAC: =SUM([USDProRataAC]) => this actuallly gives the same column as Scotts Measure - however probably gives values beyond this year
USDProRataAC PrevMonth: =if (HASONEVALUE(DateTable[MonthOfYear]),CALCULATE(sum(factFin[USDProRataAC]), DATEADD(DateTable[DateKey], -1, MONTH)))
USDProRataAC ThisMonth: =[Sum of USDProRataAC]-[USDProRataAC PreviousMonth]

The [USDProRataAC ThisMonth] is giving the correct value for all months but January as it deducts December. It also gives wrong number after the cut-off/ reporting month if there values after the cut-off /reporting month in the database. Scotts formula helps with solving this cut-offs correctly.

I realise that I might be asking too much help here but if someone have time I would really appreciate it.

Brgds,
Inge

 

[scottsen]

I am... confused

factFin[AmountUSDProRata] -- is that a running total, or an individual month value?
* If it's a running total, why are we writing running total measures?
* If it's an individual month already, why are we subtracting this month and last month to get ... data we already have?

Anyway...

If we wrote "this month" as:
=CALCULATE(SUM(factFin[AmountUSDProRata]), FILTER(dimMonths, dimMonths[MonthId] <= MAX(dimMonths[MonthId])), VALUES(dimMonths[MonthOfYear]))

Then i would write "last month" as:
=CALCULATE(SUM(factFin[AmountUSDProRata]), FILTER(dimMonths, dimMonths[MonthId] <= MAX(dimMonths[MonthId])-1), VALUES(dimMonths[MonthOfYear]))

Because we are constraining the Year to "this year" (via the VALUES() weirdness)... I would expect that -1 to roll into "before Janurary", and return no rows. Which ... should be fine?

 

[ilovasen]

Scott, I am really grateful for your efforts. If I try to explain it again, can you please give it one more shot, please?

Maybe I am no clear here. I will try to explain again:

[AmountUSDProRata] is a running total per account in the accounting system aggregating per month. Balance Sheet accounts aggregates even from year to year but Profit & Loss (P&L) accounts starts from zero at the year start.
As I have changed the date table since my first post - (defined it Date-table in "Mark as Date table") I am using DateTable[DateKey] as a date column (day granularity over 20 years) and the DateTable[MonthOfYear] as the month number (1-12 per year - defined as Whole Number).

I can easily calculate This Month and Previous Month numbers but these will be Running totals of Year-to-Date (YTD) numbers as the [AmountUSDProRata] is originally stored as a YTD number (Running total). Subtracting the YTD for an actual month (say March YTD) with the previous month (February (Last month = Previous month)) would give me the actual month value of March (not the YTD number). This works fine for all months but January, because then the formula calculates January less December. This is ok for Balance Sheet accounts as they accumulates over the years but not for P&L accounts which starts from zero every January.

What I would have done in Excel is something like the following:
=[AmountUSDProRata ThisMonth]-if(DateTable[MonthOfYear]=1,0,[AmountUSDProRata PreviousMonth]) "(=> second part should be zero as you should not subtract previous month (December) if the [MonthofYear] is 1 i.e. January.)" Wen I try this in DAX it gives me "Calculation error in measure 'factFin'[USDProRataAC ThisMonth]: The value for column 'MonthOfYear' in table 'DateTable' cannot be determined in the current context."

My problem is to find the January number (from which I will base all the reporting thereafter. January for the P&L accounts will be [AmountUSDProRata ThisMonth] in itself (i.e. NOT deducting previous month) while for all other months the number I am looking for is the difference between This Month YTD (i.e. Running total) an Previous Month YTD (i.e. Running total).

I hope this was slightly more clear. (All this would not have been a problem if the Accounting system stored the data as month values for P&L accounts and NOT YTD numbers).

Thanks in advance. Brgds, Inge

 

[ilovasen]

----
Here is an example

Scott, your formulas are represented in Test and Test-1; they would be perfect i they deducted PreviousMonthYTD with ThisMonthYTD for M03. What I would like to achieve is to get the ThisMonth for M01 and M04 as 0 - they should both ignore the previous month. For M01 because it starts from 0 and therefore Decembers YTD should be ignored. For M04 because this is Next Month which has not happened yet and there should be no value there.

[TABLE="width: 598"]
<tbody>[TR]
[TD]FyYearLabel[/TD]
[TD]FyMonthLabel[/TD]
[TD]bol[/TD]
[TD]This
MonthYTD[/TD]
[TD] Previous
MonthYTD[/TD]
[TD]ThisMonth[/TD]
[TD] Test[/TD]
[TD]Test-1
[/TD]
[/TR]
[TR]
[TD]FY14[/TD]
[TD]M01[/TD]
[TD]10204[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]18,911[/TD]
[TD="align: right"]-18,911[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]M02[/TD]
[TD]10204[/TD]
[TD="align: right"]4,158[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]4,158[/TD]
[TD="align: right"]4,158[/TD]
[TD="align: right"]4,158[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]M03[/TD]
[TD]10204[/TD]
[TD="align: right"]5,961[/TD]
[TD="align: right"]4,158[/TD]
[TD="align: right"]1,804[/TD]
[TD="align: right"]5,961[/TD]
[TD="align: right"]5,961[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]M04[/TD]
[TD]10204[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]5,961[/TD]
[TD="align: right"]-5,961[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]M05[/TD]
[TD]10204[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]M06[/TD]
[TD]10204[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]M07[/TD]
[TD]10204[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]M08[/TD]
[TD]10204[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]M09[/TD]
[TD]10204[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]M10[/TD]
[TD]10204[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]M11[/TD]
[TD]10204[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]M12[/TD]
[TD]10204[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD]FY14 Total[/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]10,119[/TD]
[TD][/TD]
[TD="align: right"]10,119[/TD]
[TD="align: right"]10,119[/TD]
[TD="align: right"]10,119
[/TD]
[/TR]
</tbody>[/TABLE]