Forum Discussion

mattknight1986's avatar
mattknight1986
Frequent Visitor
8 years ago
Solved

Help with temporal totals (TOTALYTD)

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

Need Help
  • MFelix's avatar
    MFelix
    8 years ago

    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's avatar
    MFelix
    8 years ago

    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

5 Replies

  • MFelix's avatar
    MFelix
    Icon for Super User rankSuper User
    8 years ago

    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's avatar
      mattknight1986
      Frequent Visitor
      8 years ago

      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's avatar
        MFelix
        Icon for Super User rankSuper User
        8 years ago

        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