WoW trend calculation

[PhilST]

Hi all,

I am working on a dashboard for some days now but I am unable to find a solution to my problem of including WoW changes for data on a weekly basis. I would love to include a third column to the right, that shows the WoW change.

The data I have is rather simple with the following two columns.

rec_week    receipts

201803       561

201804      1594

201805       846

201806       953

...

...

Thanks so much in advance! I checked several threads for this, but non worked somehow. I am relatively new to Power BI, btw.

Philipp

[MFelix · Accepted solution]

HI @PhilST,

If you just want to calculate the WoW add the following measure to your model then put it on a table visual.

WoW =
VAR weekselection =
    MAX ( Table1[rec_week] )
RETURN
    SUM ( Table1[receipts] )
        - CALCULATE (
            SUM ( Table1[receipts] );
            FILTER ( ALL ( Table1[rec_week] ); Table1[rec_week] = weekselection - 1 )
        )

Be aware that this won't work for previous year, do you need to pick from last week of previous year also?

Regards,

MFelix

[PhilST]

 MFelix,

Thanks for the quick response!

I get the following error message when adding the measure you proposed:

"The syntax for ';' is incorrect. (DAX(VAR weekselection = MAX ( Table1[rec_week] )RETURN SUM ( Table1[receipts] ) - CALCULATE ( SUM ( Table1[receipts] ); FILTER ( ALL ( Table1[rec_week] ); Table1[rec_week] = weekselection - 1 ) )))."

Is there something I should adapt in the measure that you proposed or prepare beforehand?

I would need the same for YoY data, yep...

Thanks in advance!

Philipp

[MFelix]

Hi @PhilST

Replace the dot comma ";" by comma "," should work as to do with DAX sintax and OS regional settings in my case it's dot comma.

[PhilST]

Thanks a lot! Now it works. I see the WoW change as absolute value now. When I try to calculate the % out of it though, it takes the same row´s data, hence the % shows wrong. Would you please also be able to help on that? I assume I need a new measure for it?

A colleague with SQL knowledge tried to help but it seems like the language is slightly different.

Thanks in advance!

Philipp

[MFelix]

Hi @PhilST,

To get the YOY changes and even if you don't have leaps in weeks you should change your measure to the folllowing:

WoW =
VAR weekselection =
    MAX ( Table1[rec_week] )
RETURN
    SUM ( Table1[receipts] )
        - CALCULATE (
            SUM ( Table1[receipts] );
            FILTER (
                ALL ( Table1[rec_week] );
                Table1[rec_week]
                    = CALCULATE ( MAX ( Table1[rec_Week] ); Table1[rec_Week] < weekselection )
            )
        )

See result below as you can see even if I don't have data between 201645 and week 201701 and 201801 the result will be calculated.

Regarding the percentage just do this following measure:

WoW % = [WoW]/ SUM(Table1[Receipts])

OR 

WoW % =  SUM(Table1[Receipts]) / [WoW]

You should use one of the above formulas depending on what is the base line you want to use on the image below you have the first calculation.

Regards,

MFelix

[M_Wavemaker]

Hello,

Would it be possible to do this on a subset of activity? i.e if Country was a field and you wanted to calculate the WoW% change, grouped by Country?

I've been trying to adapt your solution with no luck so far.

Thanks

M

[PhilST]

Thank you very much MFelix!

Working perfectly. I really need to learn DAX 🙂