YTD MTD Date Slicer

[Anonymous]

Hello Everyone,

I am very new in powerBI. any one help me to create a date slicer of MTD & YTD.

Thanks

[Fowmy]

@Anonymous 

You need to create a table with the values of MTD, YTD... and so on, then insert a slicer based on this column of the table. Create a measure like an example below.

Result = 
IF(HASONEFILTER(Table[KPI]),
    SWITCH(SELECTEDVALUE(KPI]),
        "MTD", [Measure 1 MTD],
        "QTD", [Measure 1 QTD],
        "YTD", [Measure 1 YTD]
    ),
   BLANK()
)

Refer This video for more: https://www.havensconsulting.net/blog-and-media/creating-a-mtd/qtd/ytd-time-intelligence-slicer

________________________

Did I answer your question? Mark this post as a solution, this will help others!.

Click on the Thumbs-Up icon on the right if you like this reply 🙂

YouTube, LinkedIn

[amitchandak]

@Anonymous , You can use date slicer or relative date slicer, YTD will use end date from date slicer

https://docs.microsoft.com/en-us/power-bi/visuals/desktop-slicer-filter-date-range

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

 YTD
https://medium.com/@amitchandak.1978/power-bi-ytd-questions-time-intelligence-1-5-e3174b39f38a
QTD
https://medium.com/@amitchandak.1978/power-bi-qtd-questions-time-intelligence-2-5-d842063da839
 MTD
https://medium.com/@amitchandak.1978/power-bi-mtd-questions-time-intelligence-3-5-64b0b4a4090e

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

diff = [MTD Sales]-[last MTD Sales]
diff % = divide([MTD Sales]-[last MTD Sales],[last MTD Sales])

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"))


This Year = CALCULATE(sum('order'[Qty]),filter(ALL('Date'),'Date'[Year]=max('Date'[Year])))
Last Year = CALCULATE(sum('order'[Qty]),filter(ALL('Date'),'Date'[Year]=max('Date'[Year])-1))

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-YTD-LYTD-Week-Over-Week/m-p/1051626#M184


Appreciate your Kudos.

[Anonymous]

Dear Amit,

I required this type date slicer. 

[Greg_Deckler]

@Anonymous - You will want to create a disconnected table with your slicer values. You will then need to craft a measure that calculates the right thing depending on the item selected in the slicer (use SELECTEDVALUE).

Disconnected Table Trick as this article demonstrates: https://community.powerbi.com/t5/Community-Blog/Solving-Attendance-with-the-Disconnected-Table-Trick/ba-p/279563

Basically:

  Measure = 

    SWITCH(SELECTEDVALUE('Slicer'[Column]),

      "MTD",<mtd calculation>,

      "YTD",<ytd calculation>,

       ...

    )

You may find this helpful - https://community.powerbi.com/t5/Community-Blog/To-bleep-With-Time-Intelligence/ba-p/1260000

Also, see if my Time Intelligence the Hard Way provides a different way of accomplishing what you are going for.

https://community.powerbi.com/t5/Quick-Measures-Gallery/Time-Intelligence-quot-The-Hard-Way-quot-TITHW/m-p/434008

[Greg_Deckler]

@Anonymous - You can use the relative date slicer. Use the small down arrow in the upper right of the slicer to change the type of slicer.

[Anonymous]

@Greg_Deckler 

Thank you for your replying. I required this type slicer.

[amitchandak]

@Anonymous , is it now possible using these three links, I shared

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