cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
PhilST Frequent Visitor
Frequent Visitor

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 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

 

1 ACCEPTED SOLUTION

Accepted Solutions
Super User
Super User

Re: WoW trend calculation

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



Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!




View solution in original post

7 REPLIES 7
Super User
Super User

Re: WoW trend calculation

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



Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!




View solution in original post

PhilST Frequent Visitor
Frequent Visitor

Re: WoW trend calculation

 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

Super User
Super User

Re: WoW trend calculation

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.

 

 

 



Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!




PhilST Frequent Visitor
Frequent Visitor

Re: WoW trend calculation

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

Super User
Super User

Re: WoW trend calculation

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.

 

wow.png

 

Regards,

MFelix

 



Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!




PhilST Frequent Visitor
Frequent Visitor

Re: WoW trend calculation

Thank you very much MFelix!

 

Working perfectly. I really need to learn DAX 🙂

Highlighted
M_Wavemaker Frequent Visitor
Frequent Visitor

Re: WoW trend calculation

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

Helpful resources

Announcements
October 2019 Community Highlights

October 2019 Community Highlights

October was a busy month in the community. Read the recap article to learn about some of the events and content.

New Solution Badges

New Solution Badges

Two waves of brand new solution badges are coming! Read the article for more information on our new community badges.

Ask Amir Anything

Exclusive LIVE Community Event No. 2 – Ask Amir Anything

Next in our Triple A series: Ask Amir Netz questions about the latest updates, features and future.

Analytics in Azure virtual event

Analytics in Azure virtual event

Experience a limitless analytics service built to ingest, prep, manage, and serve data for immediate use in Power BI.

Users Online
Currently online: 135 members 1,467 guests
Please welcome our newest community members: