topic WTD, MTD, QTD and YTD calculation on aggregated data in DAX Commands and Tips

WTD, MTD, QTD and YTD calculation on aggregated data

Solle — Sat, 18 Mar 2023 08:41:53 GMT

Hi 🙂

I am currently working with some data, which is aggregated in that sense, that it is a snapshot that is taken each day which is the total.

I.e. I have the following table which does only include business days - I am using a datetable for that named DimDate

Date	Group	Market Value
08/03/2023	Net Asset Value	100
09/03/2023	Net Asset Value	102
10/03/2023	Net Asset Value	103
13/03/2023	Net Asset Value	101

I have tried using the built-in functionalities like TotalYTD etc. but they sums it all up providing me with a very large number.

Based on the above table I would like the YTD to be 101 - 100 = 1

Does anyone know how to solve this? Please let me know if anything else is required from my side to sort this.

Best Regards,
Solle

Re: WTD, MTD, QTD and YTD calculation on aggregated data

Greg_Deckler — Sat, 18 Mar 2023 18:17:28 GMT

Solle See this: Better Year to Date Total - Microsoft Power BI Community

You may find this helpful - https://community.powerbi.com/t5/Community-Blog/To-bleep-With-Time-Intelligence/ba-p/1260000

Also, see if my Time Intelligence the Hard Way provides a different way of accomplishing what you are going for.

https://community.powerbi.com/t5/Quick-Measures-Gallery/Time-Intelligence-quot-The-Hard-Way-quot-TITHW/m-p/434008

Re: WTD, MTD, QTD and YTD calculation on aggregated data

Solle — Sun, 19 Mar 2023 12:10:30 GMT

Hi Greg_Deckler
Thank you for your input, the video and in particular your very fullfilled work on the time intelligence functions helped me to come up with a solution as per below:

Solvency Summary - Solvency YTD =

VAR CurrentYear =

YEAR ( MAX ( 'FactSolvencySummary'[As Of Date] ) )

VAR CurrentValue =

CALCULATE( MAX ( 'FactSolvencySummary'[Market Value inc Accrued Interest] ), FactSolvencySummary[Attribute] = "Solvency")

VAR MinDate =

CALCULATE ( MIN ( 'FactSolvencySummary'[As Of Date] ), YEAR ( 'DimDate'[Date]) = CurrentYear )

VAR _minValue =

CALCULATE ( MIN ( 'FactSolvencySummary'[Market Value inc Accrued Interest] ), FactSolvencySummary[Attribute] = "Solvency", 'DimDate'[Date] = MinDate )

VAR PrimoUltimoValue = CurrentValue - _minValue

RETURN

PrimoUltimoValue

I will accept your response as a solution to this 🙂