How to calculate QTD and YTD based on MTD

[PBI_RH]

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

....

[tackytechtom]

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/

[PBI_RH]

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. 

[tackytechtom]

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/

[PBI_RH]

Hi Tom, 

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

thank you again for the help. 

[PBI_RH]

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