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]

I'm actually really sorry. I have done some poor reading and eventually got confused by the SUM([Amount]), thinking that taking a SUM of a running total was weird. But we could have just as well used MIN, MAX, AVERAGE, VALUES, ...

And my measures... no good.

Anyway...

"The value for column 'MonthOfYear' in table 'DateTable' cannot be determined in the current context." is secret code for "you need to wrap this column in an aggregate function". You need MAX(DateTable[MonthOfYear]) or MIN(DateTable[MonthOfYear]) or whatever. Since you have just 1 month, min and max should give the same.

You can try that.

Also, I keep using VALUES(dimMonths[MonthOfYear]), but I actually mean VALUES(dimMonths[Year]). That was my effort to constrain the calculation to the current year (return blank for January).

So, my claim is that, when used on rows with months... this would give you good values:

=SUM(factFin[AmountUSDProRata]) - CALCULATE(SUM(factFin[AmountUSDProRata]), FILTER(dimMonths, dimMonths[MonthId] = MAX(dimMonths[MonthId])-1), VALUES(dimMonths[Year]))

In English: This month's amount column - the amount column when filtered to 1 month ago AND constrained to the same year.

Honestly though, unless you have a lot of rows (say > 100k), I would probably do this work in a calculated column, rather than a measure...

 

[ilovasen]

Hi
Think I have solved it myself:

ACPR ThisMonth:= =IF( HASONEVALUE( DateTable[MonthofYear]), IF( (VALUES( DateTable[MonthofYear]) = 1 || VALUES(DateTable[NextMonth]) = 1), [Sum of USDProRataAC], [USDProRataAC ThisMonth] ) , BLANK() )

Where
USDProRataAC ThisMonth:= [Sum of USDProRataAC]-[USDProRataAC PreviousMonth]
and new Calculated Column:
NextMonth = IF( [YearKey]=YEAR([Last Fiscal Date]) && [MonthOfYear]=(MONTH([Last Fiscal Date])+1) ,1 ,0 )

Thanks again to Scott for his support for solving this.

Brgds,
Inge