Calculate MTD QTD YTD measures based on Selected Date in a Slicer

[Optimusprime_25]

Hi,

We have two columns  Sales and Date in a table.

We have a requirement where we need to show MTD, QTD and YTD for Sales column in matrix visual based on a selected date.

		Date	10/05/2022
Category	YTD Sales	QTD Sales	MTD Sales
A	100	70	25
B	150	100	35
C	120	80	30

Here in the Matrix

YTD column should calculate Sales from 01/01/2022 - 10/05/2022

QTD column should calculate Sales from 01/04/2022 - 10/05/2022

MTD column should calculate Sales from 01/05/2022 - 10/05/2022

Can anyone help me out?

Thanks in advance.

[amitchandak]

@Optimusprime_25 , You can use datesqtd, datesmtd, and datesytd with the date table

MTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESMTD('Date'[Date]))

QTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESQTD(('Date'[Date])))

YTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD('Date'[Date],"12/31"))

Power BI — Year on Year with or Without Time Intelligence
https://medium.com/@amitchandak.1978/power-bi-ytd-questions-time-intelligence-1-5-e3174b39f38a
https://www.youtube.com/watch?v=km41KfM_0uA
Power BI — Qtr on Qtr with or Without Time Intelligence
https://medium.com/@amitchandak.1978/power-bi-qtd-questions-time-intelligence-2-5-d842063da839
https://www.youtube.com/watch?v=8-TlVx7P0A0
Power BI — Month on Month with or Without Time Intelligence
https://medium.com/@amitchandak.1978/power-bi-mtd-questions-time-intelligence-3-5-64b0b4a4090e
https://www.youtube.com/watch?v=6LUBbvcxtKA

[Optimusprime_25]

Hi @amitchandak  Thanks for your prompt response.

Sorry, I didn't mention in the post, we have drill down on category, when we drill down Sales should come as depicted below in the table.

YTD will display all fields from January to selected date, QTD should display only those fields which are applicable for that quarter and MTD should display only those fields which are applicable for that month.

Sales should come as blank if any field sales is not present in QTD and MTD Sales.

Category	YTD	QTD	MTD
A	100	75	25
A1	25		25
A2	25	25
A3	20	20
A4	30	30
B	150	100	35
B1	25	25
B2	35	35	35
B3	10	10
B4	80

Thanks you.