YTD calculation - In month values?

[john_whelan]

Hi, 

Can someone help me please? 

I'm brand new to PBI and I'm looking for a simple YTD calculation however, the data I have is already cumulative? 

I'm aware that there is a YTD calculation but that does not work for me because my data is cumulative. 

So, in my head, I need say, Julys result i.e. 500. 

I need to know Januarys result i.e. 200. 

(Jul) 500- (Jan) 200=(YTD)300.

Can someone give me any pointers on how to acheive this please? 

Any tips would be most appreciated? 

Thanks in adavnce for your help with this. 

Cheers

[amitchandak]

@john_whelan , Hope I got it correctly

Try with Date table. Example

YTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD('Date'[Date],"12/31"))
Last YTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD(dateadd('Date'[Date],-1,Year),"12/31"))
This year Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD(ENDOFYEAR('Date'[Date]),"12/31"))
Last year Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD(ENDOFYEAR(dateadd('Date'[Date],-1,Year)),"12/31"))
Last to last YTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD(dateadd('Date'[Date],-2,Year),"12/31"))
Year behind Sales = CALCULATE(SUM(Sales[Sales Amount]),dateadd('Date'[Date],-1,Year))

Power BI — YTD Questions — Time Intelligence 1–5
https://medium.com/@amitchandak.1978/power-bi-ytd-questions-time-intelligence-1-5-e3174b39f38a

To get the best of the time intelligence function. Make sure you have a date calendar and it has been marked as the date in model view. Also, join it with the date column of your fact/s. Refer :
https://radacad.com/creating-calendar-table-in-power-bi-using-dax-functions
https://www.archerpoint.com/blog/Posts/creating-date-table-power-bi
https://www.sqlbi.com/articles/creating-a-simple-date-table-in-dax/

See if my webinar on Time Intelligence can help: https://community.powerbi.com/t5/Webinars-and-Video-Gallery/PowerBI-Time-Intelligence-Calendar-WTD-Y...


Appreciate your Kudos.

[john_whelan]

Thanks for the quick reply Amit.

That solution would work if the data was not cumulative.

Am I taking the correct approach by trying to select the 'cumulative' result at the end of last year, then the variance between that and today is the result I'm looking for?

Thanks again for your help.

[amitchandak]

@john_whelan , so you have data only in last year dec, or every month you have YTD data.

If every month has YTD data - then use MTD

last year MTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESMTD(dateadd('Date'[Date],-12,MONTH)))

if only dec has all data

Last year Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD(ENDOFYEAR(dateadd('Date'[Date],-1,Year)),"12/31"))

//12/31 represent year end. So if have different year end use that

[john_whelan]

Thanks so much for all your help. It didn't answer the question exactly but gave a way to the solution.

The issue was because the data is Cumulative - Not in Month.

Essentially I used,

"COSTS-0 =

CALCULATE

(SUM(Data[CTD Reported Costs]),DATESMTD(dateadd(Data[Report_period],-0,MONTH)))"

to get the current month

"DEC19_COSTS =

CALCULATE

(SUM(Data[CTD Reported Costs]),

        DATESBETWEEN(Data[Report_period],

            DATE(2019,12,01),

            DATE(2019,12,01)

))"

To get the December position.  

"Costs - YTD = CALCULATE([COSTS-0]-[DEC19_COSTS])"

Gives the correct results.

Probably a better way to do this but it worked for me.

Cheers