Help with temporal totals (TOTALYTD)

[mattknight1986]

I've been having significant issues with getting TOTALYTD functions to work, having tried out a number of different permutations and combinations, all yielding the same result.

First and foremost I'm trying to analyse a trial balance, using in-month and YTD variances. For some reason, my YTD DAX functions aren't working, returning only the in-month amount (and not returning any values for dates preceding this year).  NB Financial year runs from April-March, which I've unambiguously treated in DAX via the "31 March" string for y/e date [DAX figures it out if I put 31/3 or 3/31 - I;ve tried both and get the same results).

There's surely one piece of logic in the calculation I'm missing or misunderstanding, so if anyone can help me find what this is and rectify, I'll be hugely grateful.

Sample .pbix file is HERE

Thanks in advance

Matt

[MFelix · Accepted solution]

Hi @mattknight1986,

For the time inteligence function to work you need to have full calendar table not only the start of the month.

I added a Dim_date table and the change you formula to:

YTDActual =
TOTALYTD (
    SUM ( TrialBalance[Period Actual SUM] );
    DATESYTD ( dimdate[Date]; "31-03" );
    ALL ( dimdate[Date] )
)

I used the solution provided on this link.

Here is the PBIX file with changes.

check if this is what you need.

Regards,

MFelix

[MFelix · Accepted solution]

Hi @mattknight1986,

For the time inteligence function to work you need to have full calendar table not only the start of the month.

I added a Dim_date table and the change you formula to:

YTDActual =
TOTALYTD (
    SUM ( TrialBalance[Period Actual SUM] );
    DATESYTD ( dimdate[Date]; "31-03" );
    ALL ( dimdate[Date] )
)

I used the solution provided on this link.

Here is the PBIX file with changes.

check if this is what you need.

Regards,

MFelix

[mattknight1986]

Thanks @MFelix - this gets me part way there, insofar as it gets the sums working correctly.

My issue now is that the built-in date hierarchy is treating April as being in Q2, when it's actually in Q1, and the Year [Calendar] shouldn't be a dividing line, as it should be financial year. So when I try to allocate these out in the dimdate table, I can make the maths work, but the months can't be sorted in order of date due to the many : one relationship. I've not been able to square this circle so far, though I am racking my brain to figure it out.

Any thoughts would be appreciated!

[MFelix · Accepted solution]

Hi @mattknight1986,

Made a few changes to the DimDate and added columns to calculate the ficas year and quarter and also a column to sort the column month year trough the modeling tab.

In attach the PBIX file.

Believe it as what you need.

Regards,

Mfelix

[mattknight1986]

Many thanks for your help @MFelix, much appreciated. I found that your YTD formula reset the total at January 1, so I tweaked it to the following:

YTDActual = 
TOTALYTD (
    SUM (TrialBalance[Period Actual SUM]),
    dimdate[Date],
    ALL (dimdate[Date]),
    "31/03"
)

But the dimdate table was the key to getting this right. What I don't get is why sorting 'monthyear' by 'Sorting_Month_Year' works, when there is more than one value in that column for the same value in 'monthyear', unless I'm missing something?

[MFelix]

Hi @mattknight1986,

The columns can be sorted by another columns so when I have one column sorted by date and then use another column that is sort by that one you will get the information sorted as you want.

Check this link.

Regards,

MFelix