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,

 

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's avatar
    PhilST
    Regular Visitor
    7 years ago

     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's avatar
      MFelix
      Super User
      7 years ago

      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's avatar
        PhilST
        Regular Visitor
        7 years ago

        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