# Annual Running Totals - First Period Values



## ilovasen (Jun 24, 2014)

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 (Jun 24, 2014)

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


----------



## ilovasen (Jun 25, 2014)

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


----------



## scottsen (Jun 25, 2014)

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 (Jul 18, 2014)

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 (Jul 18, 2014)

=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 (Jul 21, 2014)

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 (Jul 21, 2014)

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 (Jul 22, 2014)

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 (Jul 22, 2014)

----
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.


FyYearLabelFyMonthLabelbolThis
MonthYTD Previous
 MonthYTDThisMonth   TestTest-1
FY14M0110204018,911-18,91100M02102044,15804,1584,1584,158M03102045,9614,1581,8045,9615,961M041020405,961-5,96100M051020400000M061020400000M071020400000M081020400000M091020400000M101020400000M111020400000M121020400000FY14 Total10,11910,11910,11910,119


<tbody>

</tbody>


----------



## ilovasen (Jun 24, 2014)

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 (Jul 22, 2014)

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 (Jul 23, 2014)

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


----------

