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 mnayar

  Thanks for your prompt response, will try and give more detail this time.

 

I have a Report that shows the GL Actual and GL YTD in separate columns. I have 4 slices YEAR Month  Branch and Service.

 

The report should be Filtered by Year and Month to Give a Month to Date / Year to date Figure in the 2 Columns Which it does. and corresponds to the Accounting system.

 

When the other 2 Slices are used it should allow filtering by Branch and Service, which it does for the totals but as shown by the example the YTD is not calculating for certain lines.

 

Pic1 Shows the   January Report for Branch 20 Service 60  and is correct

 

 Pic1

 

 Pic2 Shows the June report with the correct total but the line for Sundry Income  1363.64 should appear in the YTD Column 

 

 PIC2

 

GL Actual

    GL Actual = IF(min(GLEntries[Group Name]) ="Income",SUMX(GLEntries,GLEntries[IncomeCalc])+0,SUMX(GLEntries,GLEntries[GL Amount])+0)

 

GL YTD

     GL YTD = TOTALYTD([GLYTD],DATESYTD(dDate[Date]),"30/06")+0

 

Looking at this I suspect the YTD formula needs changing, Have tried the ALL() with no difference ?

 

 

 

Stachu's avatar
Stachu
Community Champion
8 years ago

you don't need to use TOTALYTD & DATESYTD in one measure

GL YTD = TOTALYTD([GLYTD],DATESYTD(dDate[Date]),"30/06")+0

should be

GL YTD = TOTALYTD([GLYTD],dDate[Date],"30/06")+0
GL YTD = CALCULATE([GLYTD],DATESYTD(dDate[Date]),"30/06")+0

 also, shouldnt' [GLYTD} be just [GL Actual]?

  • 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