Forum Discussion
Measure with variable filter based on slicer
Hi All,
I want to calculate the sum of sales with filter on current year:
Current situation:
1 Reply
- amitchandakSuper User
Make sure you have a powerbi date Calendar table marked as date.
https://docs.microsoft.com/en-us/dax/calendar-function-dax
https://radacad.com/creating-calendar-table-in-power-bi-using-dax-functions
Datesmtd, totalmtd should work for you.
https://docs.microsoft.com/en-us/dax/totalmtd-function-dax
MTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESMTD(ENDOFMONTH'Date'[Date]))) Last month Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESMTD(ENDOFMONTH(dateadd('Date'[Date],-1,MONTH)))) QTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESQTD('Date'[Date])) Last Qtr Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESQTD(ENDOFQUARTER(dateadd('Date'[Date],-3,MONTH)))) // Use end of qtr only if required Year Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD('Date'[Date Filer]))These will respond to the date in the slicer . Remove endofthe ... to get till date only.
Appreciate your Kudos. In case, this is the solution you are looking for, mark it as the Solution. In case it does not help, please provide additional information and mark me with @
Thanks.
My Recent Blog - https://community.powerbi.com/t5/Community-Blog/Comparing-Data-Across-Date-Ranges/ba-p/823601