Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

YTD DAX

I am looking for a YTD measure WHICH

will give me percent growth rate change for YTD the data available for latest month as compared to previous year.

For example IF the latest data available is till APRIL 2022 then Query should return

[Sum(January 2022-April 2022 ) - Sum (January 2021 - April 2021)] / Sum (January 2021 - April 2021)

1 ACCEPTED SOLUTION
amitchandak
Super User
Super User

@Anonymous , Try measure like this using time intelligence

 

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

 

 

or

 

YTD QTY forced=
var _max = eomonth(today(),-1)
return
if(max('Date'[Date])<=_max, calculate(Sum('order'[Qty]),DATESYTD('Date'[Date])), blank())
//or
//calculate(Sum('order'[Qty]),DATESYTD('Date'[Date]),filter('Date','Date'[Date]<=_max))
//calculate(TOTALYTD(Sum('order'[Qty]),'Date'[Date]),filter('Date','Date'[Date]<=_max))

 

 

LYTD QTY forced=

var _today = eomonth(today(),-1)
var _max = date(year(_today )-1,month(_today ),day(_today ))
return
if(max('Date'[Date])<=_max, CALCULATE(Sum('order'[Qty]),DATESYTD(dateadd('Date'[Date],-1,year)),'Date'[Date]<=_max), blank())
//OR
//CALCULATE(Sum('order'[Qty]),DATESYTD(dateadd('Date'[Date],-1,year)),'Date'[Date]<=_max)
//TOTALYTD(Sum('order'[Qty]),dateadd('Date'[Date],-1,year),'Date'[Date]<=_max)

 

View solution in original post

5 REPLIES 5
v-rzhou-msft
Community Support
Community Support

Hi @Anonymous ,

 

I think you can try my code to create a measure. It is dynamic.

I create a related Dimdate table.

DimDate = 
CALENDARAUTO()

The data in my data table is from 2021/01/01 to 2022/04/30.

Measure:

YTD DIFF PERCENTAGE = 
VAR _THISYEAR_ENDDATE =  CALCULATE( MAX('Table'[Date]),ALL(DimDate))
VAR _THISYEAR_STARTDATE = DATE(YEAR(_THISYEAR_ENDDATE),1,1)
VAR _LASTYEAR_ENDDATE = EOMONTH(_THISYEAR_ENDDATE,-12)
VAR _LASTYEAR_STARTDATE = DATE(YEAR(_THISYEAR_ENDDATE)-1,1,1)
VAR _CURRENT_YTD = CALCULATE(SUM('Table'[Value]),FILTER(ALL(DimDate),AND( DimDate[Date]>=_THISYEAR_STARTDATE,DimDate[Date]<=_THISYEAR_ENDDATE)))
VAR _PREVIOUS_YTD = CALCULATE(SUM('Table'[Value]),FILTER(ALL(DimDate),AND( DimDate[Date]>=_LASTYEAR_STARTDATE,DimDate[Date]<=_LASTYEAR_ENDDATE)))
RETURN
DIVIDE(_CURRENT_YTD - _PREVIOUS_YTD,_PREVIOUS_YTD)

Result is as below.

RicoZhou_0-1652683253255.png

 

Best Regards,
Rico Zhou

 

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

 

 

johncolley
Solution Sage
Solution Sage

Hi,

 

There may be a more elegant solution but I was able to produce the end result with the following:

 

YTDValues = 

VAR month = 4

return

CALCULATE([YTD Value],'Calendar'[Month] <= month)

**Please note i set Var month = 4 here for April with the dummy data. You can replace with Var month = Month(Today()) for a dynamic solution.

 

%changemeasure = 

Var cytd = CALCULATE([YTDValues],'Calendar'[Year] = 2022)

Var pytd = CALCULATE([YTDValues],'Calendar'[Year] = 2021)

return

(cytd - pytd) / pytd * 100

johncolley_0-1652338222437.png

 

Anonymous
Not applicable

We already have this hardcoded. I am looking for dynamic query

amitchandak
Super User
Super User

@Anonymous , Try measure like this using time intelligence

 

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

 

 

or

 

YTD QTY forced=
var _max = eomonth(today(),-1)
return
if(max('Date'[Date])<=_max, calculate(Sum('order'[Qty]),DATESYTD('Date'[Date])), blank())
//or
//calculate(Sum('order'[Qty]),DATESYTD('Date'[Date]),filter('Date','Date'[Date]<=_max))
//calculate(TOTALYTD(Sum('order'[Qty]),'Date'[Date]),filter('Date','Date'[Date]<=_max))

 

 

LYTD QTY forced=

var _today = eomonth(today(),-1)
var _max = date(year(_today )-1,month(_today ),day(_today ))
return
if(max('Date'[Date])<=_max, CALCULATE(Sum('order'[Qty]),DATESYTD(dateadd('Date'[Date],-1,year)),'Date'[Date]<=_max), blank())
//OR
//CALCULATE(Sum('order'[Qty]),DATESYTD(dateadd('Date'[Date],-1,year)),'Date'[Date]<=_max)
//TOTALYTD(Sum('order'[Qty]),dateadd('Date'[Date],-1,year),'Date'[Date]<=_max)

 

Anonymous
Not applicable

this gives the required number but still i am unable to implement this

 

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.