Yearly sales based on selection

[Prabha45]

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.

[v-jialluo-msft · Accepted solution]

^{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}

[Prabha45]

Thank you that worked

[djurecicK2]

Hi @Prabha45 , 

 Are you using a separate date table?

https://www.sqlbi.com/articles/creating-a-simple-date-table-in-dax/

[Prabha45]

Yes, having a date table. Both the tables having many to one relationship