Calculate the YTD Revenue and Previous YTD Revenue

DAX DAX Calculations
1

Hello Team,

I need help understanding the best way to calculate my YTD Revenue and Previous YTD Revenue I am working with a sales data set that has data for 2017 and 2018 and the data for year 2018 ends at Sep 30th 2018 how can i write a DAX formula to calculate the YTD Revenue and Previous YTD Revenue taking into consideration that the Sales data for 2018 ends on Sep 30th 2018.

Thank you

2

Hi @smusa

For YTD you can use CALCULATE and DATESYTD functons

More you can find at Master DAX course section:

Prerequisit for use Time Intelligence functions is continuous Date dimensions (with all dates whenever you have your sales figures or not)

Do you want YTD to stop on Sep 30th 2018 or you have sales figures up to Sep 30th 2018 ?

If you want to stop - what you would like to do with 2017 ?

3

You can have something like this

image

$ Stays YTD = 

VAR LastReservationDate = [Max Reservation Date]
VAR YTDStays =CALCULATE([$ Stays],DatesYTD(dim_Calendar[Date]))
RETURN 
IF (LASTDATE(dim_Calendar[Date] )>LastReservationDate, BLANK(),
YTDStays)

$ Stays YTD PY = CALCULATE([$ Stays YTD],DATEADD(dim_Calendar[Date],-1,YEAR),ALL(dim_Calendar))

$ Stays YTD 2PY = CALCULATE([$ Stays YTD],DATEADD(dim_Calendar[Date],-2,YEAR),ALL(dim_Calendar))
4

Regarding Time Intelligence prerequisites Melissa made such great explanation in serial of posts + provide code.

Maybe you can start from:

and follow the links.

Good luck.

1 Like
5

Hello @mspanic and thank you for your response.
I have sales data up to Sep 30th 2018 and i want to analyze and compare 2017 and 2018 data.I used the time intelligence function and it worked .

1 Like