Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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 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.
Solved! Go to 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] ) )
Best Regards,
Dale
@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
Pic2 Shows the June report with the correct total but the line for Sundry Income 1363.64 should appear in the YTD Column
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]?
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] ) )
Best Regards,
Dale
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
Hi @AustralPRB,
Can you share the file? You can delete or mask the sensitive parts.
Best Regards,
Dale
I can send you pbix file
User | Count |
---|---|
140 | |
113 | |
104 | |
77 | |
65 |
User | Count |
---|---|
136 | |
117 | |
101 | |
71 | |
61 |