Cumulative YTD Measure Query

[Saes]

Hello all, I'm hoping someone can help with a measure query I have regarding like-for-like YTD sales for previous years.

I need to compare the cumulative number of products sold on a month-by-month against previous years. The visual output is a vertical bar chart using the following:

- the x-axis displays the products

- the y-axis the number of products sold (YTD)

- the legend shows the years, which are selected through a separate filter

I've tried using the TotalYTD function, but this shows the full year count for previous years, as opposed to like-for like on a month-by-month basis for equivalent periods in previous years. Also, I need the YTD to run from 1 April to 31 March

I've seen various videos and posts where the a solution has been proposed with the year coded into separate measures, but I'd like to avoid this; the users of this dashboard would like it to be dynamic so they select as many years as they'd like.

Any help would be gratefully received! Thanks.

[dax]

Hi  @Saes , 

If possible could you please inform me more detailed information(such as your expected output and your sample data )? Then I will help you more correctly.

Please do mask sensitive data before uploading.

Thanks for your understanding and support.
Best Regards,
Zoe Zhi

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

[amitchandak]

@Saes , you can use time intelligence with date calendar. Ytd function allows you give year end date

YTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD('Date'[Date],"3/31"))

Last YTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD(dateadd('Date'[Date],-1,Year),"3/31"))
Year behind Sales = CALCULATE(SUM(Sales[Sales Amount]),dateadd('Date'[Date],-1,Year))

To get the best of the time intelligence function. Make sure you have a date calendar and it has been marked as the date in model view. Also, join it with the date column of your fact/s. Refer :
https://radacad.com/creating-calendar-table-in-power-bi-using-dax-functions
https://www.archerpoint.com/blog/Posts/creating-date-table-power-bi
https://www.sqlbi.com/articles/creating-a-simple-date-table-in-dax/

See if my webinar on Time Intelligence can help: https://community.powerbi.com/t5/Webinars-and-Video-Gallery/PowerBI-Time-Intelligence-Calendar-WTD-Y...


Appreciate your Kudos.