Difference (in %) actual vs previous period (day, week or month)

[Anonymous]

Hi all,

I build a nice dashboard in Power BI with drilldown functionality in every visual. You can drill down from month, to week, to day.

I have done this with a calender table and joined this with various fact tables and created measures. The calender table looks like this:

Date	Month	Week
01-01-2022	2022-01	2022-01

I now want to create a measure that compares the current value in the chart with the previous period's value in the chart and calculate the difference.

Of course, DAX functions like PREVIOUSMONTH will not work on the week and day level. I do want something similar though that reacts on the drill through level you are in.

Can anyone help with this? Any help is greatly appreciated.

[Anonymous]

Hi @v-yiruan-msft , thank you for your reply.

I tried to get it to work, depending on my hierarchy level, using this DAX statement:

Aantal aanvragen vorige periode = 

var tijdselectie = switch(true(), 
                    ISFILTERED(Kalender[Maandnr]), "Maand",
                    ISFILTERED(Kalender[Weeknr]), "Dag",
                    ISFILTERED(Kalender[Maandnr]), "Week"
                    )
var vorigeweek = calculate(max(Kalender[Weeknr - 1]), Kalender[Weeknr] = SELECTEDVALUE(Kalender[Weeknr]), all(Kalender))
var mindatumvorweek = calculate(MIN(Kalender[Datum]), Kalender[Weeknr] = vorigeweek, all(Kalender))
var maxdatumvorweek = calculate(MAX(Kalender[Datum]), Kalender[Weeknr] = vorigeweek, all(Kalender))
return 

switch(tijdselectie,
    "Week", calculate(DISTINCTCOUNT(Dossier[id Dossier]), USERELATIONSHIP('Kalender'[Datum], Dossier[Status - Datum aanvraag]), DATESBETWEEN(Kalender[Datum], mindatumvorweek, maxdatumvorweek)),
    "Maand", calculate(DISTINCTCOUNT(Dossier[id Dossier]), USERELATIONSHIP('Kalender'[Datum], Dossier[Status - Datum aanvraag]), PREVIOUSMONTH('Kalender'[Datum])),
    "Dag", calculate(DISTINCTCOUNT(Dossier[id Dossier]), USERELATIONSHIP('Kalender'[Datum], Dossier[Status - Datum aanvraag]), PREVIOUSDAY('Kalender'[Datum])))

It does not seem to work though. On every hierarchy level in the visual, it will calculate using the first value in the switch statement in the var tijdselectie. Do you know what is going wrong?

Every calculate on its own in the return works fine when I run them without the switch.

[v-yiruan-msft · Accepted solution]

Hi @Anonymous ,

Please try to update the formula of measure [Aantal aanvragen vorige periode] as below and check if it can return the correct result...

Aantal aanvragen vorige periode =
VAR selday =
    ISINSCOPE ( Kalender[Day] )
VAR selweek =
    ISINSCOPE ( Kalender[Weeknr] )
VAR selmonth =
    ISINSCOPE ( Kalender[Maandnr] )
VAR vorigeweek =
    CALCULATE (
        MAX ( Kalender[Weeknr - 1] ),
        Kalender[Weeknr] = SELECTEDVALUE ( Kalender[Weeknr] ),
        ALL ( Kalender )
    )
VAR mindatumvorweek =
    CALCULATE (
        MIN ( Kalender[Datum] ),
        Kalender[Weeknr] = vorigeweek,
        ALL ( Kalender )
    )
VAR maxdatumvorweek =
    CALCULATE (
        MAX ( Kalender[Datum] ),
        Kalender[Weeknr] = vorigeweek,
        ALL ( Kalender )
    )
RETURN
    SWITCH (
        TRUE (),
        selweek,
            CALCULATE (
                DISTINCTCOUNT ( Dossier[id Dossier] ),
                USERELATIONSHIP ( 'Kalender'[Datum], Dossier[Status - Datum aanvraag] ),
                DATESBETWEEN ( Kalender[Datum], mindatumvorweek, maxdatumvorweek )
            ),
        selmonth,
            CALCULATE (
                DISTINCTCOUNT ( Dossier[id Dossier] ),
                USERELATIONSHIP ( 'Kalender'[Datum], Dossier[Status - Datum aanvraag] ),
                PREVIOUSMONTH ( 'Kalender'[Datum] )
            ),
        selday,
            CALCULATE (
                DISTINCTCOUNT ( Dossier[id Dossier] ),
                USERELATIONSHIP ( 'Kalender'[Datum], Dossier[Status - Datum aanvraag] ),
                PREVIOUSDAY ( 'Kalender'[Datum] )
            )
    )

If the above one can't help you get the desired result, please provide some sample data in your table  (exclude sensitive data) with Text format and your expected result with backend logic and special examples. Also please share the matrix visual setting. It is better if you can share a simplified pbix file. You can refer the following link to upload the file to the community. Thank you.

How to upload PBI in Community

Best Regards

[Anonymous]

Hi @v-yiruan-msft , this helped. Thanks!

[v-yiruan-msft]

Hi @Anonymous ,

You can refer the following links to get it:

1. Day different(%)

Calculate Percentage Change from Present Day to Previous Day

---

We should only need to update the PreDate to the date of 7 days ago.

Change_7 = 
VAR CurrentDate = Table1[Date]
VAR PreDate = CurrentDate - 7
VAR PreDue =
    LOOKUPVALUE (
        Table1[Current Due],
        Table1[Date], PreDate,
        Table1[Cust No], Table1[Cust No],
        Table1[Branch], Table1[Branch]
    )
RETURN
    (
        IF ( PreDue <> BLANK (), Table1[Current Due] - PreDue )
    )

---

2. Week different(%)

Calculate Week Over Week change % in PowerBI

Get Power BI Previous Week Values Using DAX & Power Query

3. Month different(%)

MEASURES – MONTH TO MONTH PERCENT CHANGE

Calculate difference from previous month

4. Apply the proper measure base on different hierarcy level

Use IsInScope to get the right hierarchy level in DAX

If the above one can't help you get the desired result, please provide some sample data in your table  (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. You can refer the following link to upload the file to the community. Thank you.

How to upload PBI in Community

Best Regards