YTD for all years in DAX

[mayurwadhwani]

Hello Experts,

I have 3 years of data for retail and want to perform YTD calculation for all 3 years.

Fact Table Name: Sales(DateId,ProductId,Sale)

Dimension: DateDim with 3 years 2019,2020,2021

Dimension: ProductDim(ProductId,PrductDesc)

I want to plot clustered column chart having single measure showing SaleYTD against PrductDesc and Year as legend.

Example: Assume we have data for all months in 2019 and 2020. Also we have data till April 2021. We want SaleYtd should show data for 2021(Jan to April),2020(Jan to April),2019(Jan to April).So that we can plot it on clustered column chart. 

I also have year slicer on report.

Can we achieve this using DAX in single measure?

Any help or suggestion would be highly appreciated.

Thanks,

Mayur

[v-kelly-msft]

Hi  @mayurwadhwani,

Do you wanna show as below:

If so,create a column as below:

Column = 
var maxdate=CALCULATE(MAX('Table'[DateId]),ALL('Table'))
var _maxmonth= MONTH(maxdate)
Return
IF(MONTH('Table'[DateId])<=_maxmonth,'Table'[Sale],BLANK())

For the related .pbix file,pls see attached.

Best Regards,
Kelly

Did I answer your question? Mark my post as a solution!

[mayurwadhwani]

Hi @v-kelly-msft ,
Thanks for the response. I want to achieve solution using measure not calculated column.
Also I want to plote years on legends, Product Desc on X-axis and Y-axis will be the measure.

Thanks

[v-kelly-msft · Accepted solution]

Hi  @mayurwadhwani ,

You could also use a measure instead:

Column = 
var maxdate=CALCULATE(MAX('Table'[DateId]),ALL('Table'))
var _maxmonth= MONTH(maxdate)
Return
IF(MONTH('Table'[DateId])<=_maxmonth,'Table'[Sale],BLANK())

And you will see:

For the related .pbix file,pls see attached.

Best Regards,
Kelly

Did I answer your question? Mark my post as a solution!

[amitchandak]

@mayurwadhwani , Try measure like

YTD QTY forced=
var _max1 = today() //or maxx(allselected('Order'),'order'[Date])
var _max = format(_max,"MMDD")
return
calculate(Sum('order'[Qty]),DATESYTD('Date'[Date]),filter('Date', format('Date'[Date],"MMDD")<=_max))

YTD QTY forced=
var _max = today() // or // 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))

[mayurwadhwani]

Thanks @amitchandak  for the response. I tried to use above measures but no luck.
In approach 1, it allows only constant for DatesYTD function in second arguement.
Also approach 2 have some issues.