cancel
Showing results for
Did you mean:  Helper I

## YTD for all years in DAX

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

1 ACCEPTED SOLUTION  Community Support

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

5 REPLIES 5  Community Support

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  Helper I

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  Community Support

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  Super User IV

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

Proud to be a Super User!  Helper I

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. Announcements #### Welcome to the User Group Public Preview  