Forum Discussion

AustralPRB's avatar
AustralPRB
Frequent Visitor
8 years ago
Solved

Error in YTD Calc

I am trying to finish a P&L Report        Essentially I have  GL Actual , Budget , Var1 , YTD GL Actual , YTD Budget Var2           When I filter for June 2018 Branch 20  Service   600  T...
Need Help
  • v-jiascu-msft's avatar
    v-jiascu-msft
    8 years ago

    Hi AustralPRB,

     

    The root cause is no data of "Sundry Income" of June exists. The possible solution could be like below.

    GL YTD =
CALCULATE ( TOTALYTD ( [GL Actual], dDate[Date] ), ALL ( dDate[Month] ) )

    Error_in_YTD_Calc

     

    Best Regards,

    Dale

AustralPRB's avatar
AustralPRB
Frequent Visitor
8 years ago

Hi Stachu

    Thanks for your input 

                I am fairly new to power BI so the detail of the formulas is not all ways apparent to me but.

                 

                                  you don't need to use TOTALYTD & DATESYTD in one measure ?   is that you shouldn't or does it not make any difference?

 

The 2 formulas you suggested is a variation and has the same results. That is total calculates but the detail has missing data.

 

Will have to give you access to the whole report I guess, dont seem to be able to attach a file?

 

 

 

 

v-jiascu-msft's avatar
v-jiascu-msft
Microsoft Employee
8 years ago

Hi AustralPRB,

 

The root cause is no data of "Sundry Income" of June exists. The possible solution could be like below.

GL YTD =
CALCULATE ( TOTALYTD ( [GL Actual], dDate[Date] ), ALL ( dDate[Month] ) )

Error_in_YTD_Calc

 

Best Regards,

Dale

  • AustralPRB's avatar
    AustralPRB
    Frequent Visitor
    8 years ago

    I had tried to add the ALL() part to the calculation formula but used dDate.Date field . 

        Thank you, your formula works a treat.

     

     

    Regards

          Peter

     

    • AustralPRB's avatar
      AustralPRB
      Frequent Visitor
      8 years ago

      UPDATE ;-

       

      Having accepted  GL YTD = CALCULATE ( TOTALYTD ( [GL Actual], dDate[Date] ), ALL ( dDate[Month] ) ) as a solution I need to add a clarification to the results . As the report data will span multiple years the above formula will also capture Month data from previous years as the ALL() removes the filter. This is not what we need to do.

       

      After looking at the problem for some time I have concluded the only way to do this is to construct you own YTD formula.

      The link below is a presentation that gives all the details for the reasons for behind my statement.

       

      https://www.sqlbi.com/tv/time-intelligence-in-microsoft-power-bi/?nu=6171

       

      This is the formula I came up with that works. (Or seems to )

       

      GL YTD =
      VAR FirstYTD = STARTOFYEAR(dDate[Date],"30/06")
      VAR LastVisibleDate = ENDOFMONTH(dDate[Date])
      RETURN
      CALCULATE(
      GLEntries[GL Actual] ,
      DATESBETWEEN ( dDate[Date] , FirstYTD ,LastVisibleDate)

      )

       

       

      Thanks  to all .

       

      Peter