Forum Discussion

VeemalS's avatar
VeemalS
Icon for Helper I rankHelper I
4 years ago
Solved

YTD Slicer

Hello,   I am trying to create a YTD/QTD/MTD slicer but I am encountering an issue.   First I create a Table  Selection with only dates column that links to my Calendar Table ( Bi Directional Mod...
General Comment
Tips and Tricks
  • Anonymous's avatar
    Anonymous
    4 years ago

    Hi VeemalS ,

     

    If you want to change your measure result by slicer, I suggest you can try calculation group by tabular.

    For reference: Creating Calculation Groups in Power BI Desktop

    From your screenshot, I see that there are different months in same year. Do you want all month in 2021 show same result when you select YTD? And Apr/May/Jun show same results in Qtr2, Jul show results in Qtr3, when you select QTD? I am confused about the rates, what is the calculation logic of thems?

    Please share a sample file with us and show us the result you want by screenshot.

     

    Best Regards,
    Rico Zhou

     

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

amitchandak's avatar
amitchandak
Icon for Super User rankSuper User
4 years ago

VeemalS , Please use Time intellignce with date table, ine directional join

 

For measure slicer refer

 


measure slicer
https://www.youtube.com/watch?v=b9352Vxuj-M
https://community.powerbi.com/t5/Desktop/Slicer-MTD-QTD-YTD-to-filter-dates-using-the-slicer/td-p/500115
https://radacad.com/change-the-column-or-measure-value-in-a-power-bi-visual-by-selection-of-the-slicer-parameter-table-pattern
https://www.youtube.com/watch?v=vlnx7QUVYME

calculation groups
https://www.sqlbi.com/blog/marco/2020/07/15/creating-calculation-groups-in-power-bi-desktop/

 

 

QTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESQTD(('Date'[Date])))
Last QTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESQTD(dateadd('Date'[Date],-1,QUARTER)))

 

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

 

MTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESMTD('Date'[Date]))
last MTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESMTD(dateadd('Date'[Date],-1,MONTH)))

VeemalS's avatar
VeemalS
Icon for Helper I rankHelper I
4 years ago

Thanks for the solution. But with this one I will have to create mutiple measures for each time periods which is somthing not acceptable by the user. In fact I was able to resolve same as I had to switch to creation of New Table iso   a Measure.