Create % change measure that adapts to a date hierarchy in a line chart

[NunoTrigo1986]

Hi!

How can I create a measure that adapts to the time unit (year month quarter in a date hierachy" of a line chart? I want to use a measure like -> 

snitt_kvm_pris MoM% =

IF(

    ISFILTERED('datetable'[Dato]),

    ERROR("Time intelligence quick measures can only be grouped or filtered by the Power BI-provided date hierarchy or primary date column."),

    VAR __PREV_MONTH = CALCULATE([snitt_kvm_pris], DATEADD('datetable'[Dato].[Date], -1, MONTH))

    RETURN

        DIVIDE([snitt_kvm_pris] - __PREV_MONTH, __PREV_MONTH)

)" but can dynamicaly change month / quarter/year

[NunoTrigo1986]

Hi, and thanks for your reply! Unfortunately, neither of the given formulas worked. The first one did not produce the expected results, and the second one threw an error since the dateadd formula seems to not accept measures.

VAR SelectedDate = MAX('datetable'[Date])

The dataset consists of two tables: the Date Table with Date, year, quarter, and month, as well as a date column, and another table like:

.

The table has columns indicating the location, type of house, and in this case, the most important columns are "datefrom" (date), "area," and "price." I have a measure for square meter price, calculated as sum(price)/sum(area). This measure is the one you and I used, named 'snitt_kvm_pris.'

What I expect to achieve is a change in the square meter price measure over different time units, which will be dependent on the line chart. Thanks again for your help.

[v-tangjie-msft]

Hi @NunoTrigo1986 ,

Please try this measure.

snitt_kvm_pris MoM% = 
VAR __PREV_MONTH = CALCULATE([snitt_kvm_pris], DATEADD('datetable'[Dato].[Date], -1, MONTH))
RETURN
SWITCH(
    TRUE(),
    ISFILTERED('datetable'[Dato].[Year]), DIVIDE([snitt_kvm_pris] - CALCULATE([snitt_kvm_pris], SAMEPERIODLASTYEAR('datetable'[Dato].[Date])), CALCULATE([snitt_kvm_pris], DATEADD('datetable'[Dato].[Date], -1, YEAR))),
    ISFILTERED('datetable'[Dato].[Quarter]), DIVIDE([snitt_kvm_pris] - CALCULATE([snitt_kvm_pris], DATEADD('datetable'[Dato].[Date], -3, MONTH))), CALCULATE([snitt_kvm_pris], DATEADD('datetable'[Dato].[Date], -1, QUARTER))),
    ISFILTERED('datetable'[Dato].[Month]), DIVIDE([snitt_kvm_pris] - __PREV_MONTH, __PREV_MONTH),
    BLANK()
)

Or:

snitt_kvm_pris MoM% = 
VAR SelectedDate = MAX('datetable'[Dato])
VAR PrevDate = SWITCH(TRUE(),
    ISFILTERED('datetable'[Dato].[Year]), DATEADD(SelectedDate, -1, YEAR),
    ISFILTERED('datetable'[Dato].[Quarter]), DATEADD(SelectedDate, -1, QUARTER),
    ISFILTERED('datetable'[Dato].[Month]), DATEADD(SelectedDate, -1, MONTH),
    BLANK()
)
VAR AvgPrice = CALCULATE([snitt_kvm_pris], ALL('datetable'), 'datetable'[Dato] = SelectedDate)
VAR PrevAvgPrice = CALCULATE([snitt_kvm_pris], ALL('datetable'), 'datetable'[Dato] = PrevDate)
RETURN
    DIVIDE(AvgPrice - PrevAvgPrice, PrevAvgPrice)

You can refer to the following post that may be helpful to you:

Unique Time Intelligence Analysis Examples for Pow... - Microsoft Fabric Community

If the above one can't help you get the desired result, please provide some sample data in your tables (exclude sensitive data) with Text format and your expected result with backend logic and special examples. It is better if you can share a simplified pbix file. Thank you.

Best Regards,

Neeko Tang

If this post  helps, then please consider Accept it as the solution  to help the other members find it more quickly.