cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Resolver I

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.

 Date 10/05/2022 Category YTD Sales QTD Sales MTDSales 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?

2 REPLIES 2
Super User

@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
Power BI — Qtr on Qtr with or Without Time Intelligence
https://medium.com/@amitchandak.1978/power-bi-qtd-questions-time-intelligence-2-5-d842063da839
Power BI — Month on Month with or Without Time Intelligence
https://medium.com/@amitchandak.1978/power-bi-mtd-questions-time-intelligence-3-5-64b0b4a4090e

Resolver I

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.

Announcements

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

Power BI Monthly Update - August 2024

Check out the August 2024 Power BI update to learn about new features.

Fabric Community Update - August 2024

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

Top Solution Authors
Top Kudoed Authors