Forum Discussion

PhilST's avatar
PhilST
Regular Visitor
7 years ago
Solved

WoW trend calculation

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 ...
Need Help
  • MFelix's avatar
    MFelix
    7 years ago

    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

MFelix's avatar
MFelix
Super User
7 years ago

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

 

Anonymous's avatar
Anonymous
Not applicable
6 years ago

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