WTD, MTD, QTD and YTD calculation on aggregated data

[Solle]

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

[Greg_Deckler · Accepted solution]

@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-TIT...

[Solle]

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 🙂