topic How to calculate QTD and YTD based on MTD in DAX Commands and Tips

How to calculate QTD and YTD based on MTD

PBI_RH — Tue, 03 May 2022 18:54:21 GMT

Hello,

I have a table with MTD value calculated everyday by ETL team. as a report developer, how to calcuate QTD and YTD by using the MTD value? There is no daily value in the fact table.

Date MTD QTD YTD

2022-01-01 5

2022-01-02 8

.....

2022-01-30 100

2022-01-31 110

2022-02-01 2

2022-02-02 5

....

2022-02-27 70

2022-02-28 76

.....

2022-03-01 7

2022-03-02 11

....

Re: How to calculate QTD and YTD based on MTD

PBI_RH — Tue, 03 May 2022 19:03:45 GMT

I'm struggling with this question, hope someone could help me out.

Re: How to calculate QTD and YTD based on MTD

tackytechtom — Tue, 03 May 2022 19:51:02 GMT

Hi PBI_RH ,

I think one way to tackle this is to "undo" the summarization of the MTD column. On the base of the "raw" data it will be easy to just create QTD and YTD measures.

So here my try:

The "DataColumn" has the following DAX:

DataColumn = 
'Table'[MTD] -
CALCULATE (
    MAX ( 'Table'[MTD] ),
    FILTER ( 
        'Table', 
        'Table'[Date] < EARLIER ( 'Table'[Date] ) && MONTH ('Table'[Date] ) = MONTH ( EARLIER  ( 'Table'[Date] ) ) 
    )
)

Then, I just created the following (quick) measures:

DataColumn QTD = 
IF(
	ISFILTERED('Table9'[Date]),
	ERROR("Time intelligence quick measures can only be grouped or filtered by the Power BI-provided date hierarchy or primary date column."),
	TOTALQTD(SUM('Table9'[DataColumn]), 'Table9'[Date].[Date])
)

DataColumn YTD = 
IF(
	ISFILTERED('Table9'[Date]),
	ERROR("Time intelligence quick measures can only be grouped or filtered by the Power BI-provided date hierarchy or primary date column."),
	TOTALYTD(SUM('Table9'[DataColumn]), 'Table9'[Date].[Date])
)

Let me know, if this solves your issue or if you get stuck somewhere on the way 🙂

/Tom
https://www.tackytech.blog/
https://www.instagram.com/tackytechtom/

Re: How to calculate QTD and YTD based on MTD

PBI_RH — Tue, 03 May 2022 19:58:33 GMT

Hi Tom,

Unfortunately, my team has no control on the data side, I need to find a solution based on current data feed.

So I won't be able to get the daily value.

Let me try your solution to Reverse engineering the data column.

really appreciate your help.

Re: How to calculate QTD and YTD based on MTD

tackytechtom — Tue, 03 May 2022 20:00:01 GMT

Hi PBI_RH ,

The Data Column in my example above, reverse engineers the daily value for you 🙂

Try it out by using the code for a calculated cvolumn and let me know if it work!

/Tom
https://www.tackytech.blog/
https://www.instagram.com/tackytechtom/

Re: How to calculate QTD and YTD based on MTD

PBI_RH — Wed, 04 May 2022 12:35:57 GMT

Hi Tom,

The DataColumn DAX returns errro, I will continue working on it later today.

thank you again for the help.