Reply
Optimusprime_25
Resolver I
Resolver I
Partially syndicated - Outbound

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

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.

 

  Date10/05/2022
CategoryYTD SalesQTD Sales

MTD

Sales

A1007025
B15010035
C1208030

 

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.

 

 

2 REPLIES 2
amitchandak
Super User
Super User

Syndicated - Outbound

@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

Full Power BI Video 20 Hours YouTube
Microsoft Fabric Series 60+ Videos YouTube
Microsoft Fabric Hindi End to End YouTube

Syndicated - Outbound

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.

 

CategoryYTDQTDMTD
A1007525
A125 25
A22525 
A32020 
A43030 
B15010035
B12525 
B2353535
B31010 
B480  

 

Thanks you.

avatar user

Helpful resources

Announcements
March PBI video - carousel

Power BI Monthly Update - March 2025

Check out the March 2025 Power BI update to learn about new features.

March2025 Carousel

Fabric Community Update - March 2025

Find out what's new and trending in the Fabric community.

Top Solution Authors (Last Month)
Top Kudoed Authors (Last Month)