Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
AustralPRB
Frequent Visitor

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

 

 

powerbi.jpg 

 

 

When I filter for June 2018 Branch 20  Service   600  The Totals are correct but the YTD is not calculating for one of the Lines ( an entry back in Jan ) because (I think ) there is no June data.

 

(It does the same to Feb , March , April and May )

 

Is it possible for you to advise the best fix for this issue?

 

I have trolled the forums an tried most of the variations of YTD calcs  ,

 

Thanks.

 

1 ACCEPTED SOLUTION

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

Community Support Team _ Dale
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

10 REPLIES 10
Anonymous
Not applicable

@AustralPRB can you post a better resolution image cant read anything in this image. 

 

also if I understand correctly you are try to create some form of avg and the calculation is taking more months than it should or less months i.e. shoudl june be counted or not counted?

 

if you dont want it to be counted, for every year calculate the number of months you have data for and divide by that.

 

calculate(distinctcount(month(table1[date])), allexcept(table1,year(table1[date])) and use this as denominator.

 

if you want june to be counted

 

calculate max month number per year and dividide by that or if you want till last month then you can calculate month(today()) - 1 or something of that variation.

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 ?

 

 

 

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]?



Did I answer your question? Mark my post as a solution!
Thank you for the kudos 🙂

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?

 

 

 

 

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

Community Support Team _ Dale
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

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

 

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

 

 

Pic1Pic1Pic2Pic2

Hi @AustralPRB,

 

Can you share the file? You can delete or mask the sensitive parts.

 

Best Regards,

Dale

Community Support Team _ Dale
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

I can send you pbix file 

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.