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 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.

Need Help

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

Best Regards,

Dale

10 Replies

Anonymous
Not applicable
8 years ago
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.
- 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
  
  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 ?
  - AustralPRB
    Frequent Visitor
    8 years ago
    Pic1Pic2