cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
mayurwadhwani
Helper I
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

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:

v-kelly-msft_0-1619603414798.png

For the related .pbix file,pls see attached.

 

Best Regards,
Kelly

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

View solution in original post

5 REPLIES 5
v-kelly-msft
Community Support
Community Support

Hi  @mayurwadhwani,

 

Do you wanna show as below:

v-kelly-msft_0-1619405743661.png

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!

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

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:

v-kelly-msft_0-1619603414798.png

For the related .pbix file,pls see attached.

 

Best Regards,
Kelly

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

View solution in original post

amitchandak
Super User IV
Super User IV

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

 



Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!
Dashboard of My Blogs !! YouTube Channel !! Connect on Linkedin

Proud to be a Super User!

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.

Helpful resources

Announcements
PBI User Groups

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group!

MBAS on Demand

2021 Release Wave 2 Plan

Power Platform release plan for the 2021 release wave 2 describes all new features releasing from October 2021 through March 2022.

July 2021 Update 768x460.png

Check it out!

Click here to read more about the July 2021 Updates

Top Solution Authors
Top Kudoed Authors