Calculate YTD based on an existing Measure

[LeoST]

Hello all,

my table looks like this:

Cost	Start	End
20	01.05.2020	31.12.2020
40	01.02.2020	31.11.2021

Now I've created a measure that shows me the costs per month for each month already:

Total Cost Monthly = 
CALCULATE(
	SUM('DataTable'[Cost]), 
    FILTER(
        ALLSELECTED(DataTable), 
        (DataTable[Start].[Date] <= MAX(DataTable[Start].[Date]) && ((DataTable[End].[Date] >= MAX(DataTable[Start].[Date])) || (ISBLANK(DataTable[End].[Date]) ))
    )
))

This works fine when I use the Start Column as a axis and shows me the sum of all costs for each month.

However I can't get the total cost for a year because the YTD function based on my Measure "Total Cost Monthly" and the start date. This calculates almost the same values as my "Total Cost Monthly" measure.

Is there any trick to get the YTD working or any other way to calculate the yearly values?

[Anonymous]

Yeah, there is a trick. It'll all be working fine if you start building correct models and use dedicated Date tables instead of the automatic ones. Automatic date tables are only for the simplest of models and should be avoided in professional development like the plague.

[amitchandak]

@LeoST ,

Can you share sample output in table format? Or a sample pbix after removing sensitive data.

Refer if these can help

https://community.powerbi.com/t5/Community-Blog/HR-Analytics-Active-Employee-Hire-and-Termination-tr...

or this file

https://www.dropbox.com/s/bqbei7b8qbq5xez/leavebetweendates.pbix?dl=0

[LeoST]

Hello @amitchandak 

Thanks for your quick response and the link - it was very helpful!

My datamodel now looks almost identical as your HR example. The formula for "Current Employees" also works perfectly for me except that I need the sum and not count since I calculate with costs and not employees. So that gives me reliably the total costs per month for each month individually.

Now what I try to do is something like a running total over that adopted "Current Employees" measure (doesn't make any sence for employee counts but in my case with costs it does and gives me the total costs in that year that).

Example:

 So in July 20 I've -136 Cost and in May 21 I've -140 Cost. My measure should show me know the total cost for a year like 2020 or 2021 (so I need to sum up the values again). 

[amitchandak · Accepted solution]

@LeoST , I tried this on current employee

Measure = CALCULATE(sumx(SUMMARIZE('Date', 'Date'[Date],"_1",[Current Employees]),[_1]),filter(ALLSELECTED('Date'),'Date'[Date] <=max('Date'[Date])))

you might have to use month year

Measure = CALCULATE(sumx(SUMMARIZE('Date', 'Date'[Month Year],"_1",[Current Employees]),[_1]),filter(ALLSELECTED('Date'),'Date'[Date] <=max('Date'[Date])))

[LeoST]

Thanks a lot, that works perfectly! 

I think I understood now how to handle dates in Power BI in the right way - thanks for that!