Forum Discussion
Yearly sales based on selection
I have two slicers site and period(Year, month) and a matrix having categories, sub categories as rows and sales as values. Now I need to create a dax where it shows total year sales of sub categories based on a month selection. If the user clicks on April under 2020 for one of the site, the Dax should show complete sales of the sub categories for that month year. If June 2020 is selected, the value should be same. The value should change only when a month from another year is selected and when site is changed.
Hi Prabha45 ,
According to your description, you need to calculate the annual sales of the subcategories based on the selection of the slicer, and ignore the influence of the month.
You can try this measure:
SALES = CALCULATE(SUM(SALES[Amount]),FILTER(ALL(SALES),SALES[DATE].[Year] = MAX(SALES[Product].[Year])))If this is not what you need, please provide sample data to address further.
Best Regards,
Gallen Luo
4 Replies
Hi Prabha45 ,
Are you using a separate date table?
https://www.sqlbi.com/articles/creating-a-simple-date-table-in-dax/
Yes, having a date table. Both the tables having many to one relationship
Hi Prabha45 ,
According to your description, you need to calculate the annual sales of the subcategories based on the selection of the slicer, and ignore the influence of the month.
You can try this measure:
SALES = CALCULATE(SUM(SALES[Amount]),FILTER(ALL(SALES),SALES[DATE].[Year] = MAX(SALES[Product].[Year])))If this is not what you need, please provide sample data to address further.
Best Regards,
Gallen Luo
Thank you that worked
