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

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