Forum Discussion

danzrust's avatar
danzrust
Helper IV
9 years ago

Week Over Week Calculations

Hi guys,

There are few similar topics on this forum, but I have a feeling the all of them are trying to solve slighly different scenarios.

 

I am playing with MoM and WoW calculations.

 

I was able to do MoM via "Quick Measures" (in beta, you need to activate it). 

 

I was then hoping I would take the MoM expression and edit it slighly to create WoW.

 

This is the DAX expression Power BI generates for MoM:

Leads MoM% =
IF(
ISFILTERED('Calendar'[Date]),
ERROR("Time intelligence quick measures can only be grouped or filtered by the Power BI-provided date hierarchy."),
VAR __PREV_MONTH =
CALCULATE(
SUM('FactTable'[Leads]),
DATEADD('Calendar'[Date].[Date], -1, MONTH)
)
RETURN
DIVIDE(SUM('FactTable'[Leads]) - __PREV_MONTH, __PREV_MONTH)
)

 

I found out that making WoW out of this is more difficult than expected.

 

I guess I need to edit this piece: DATEADD('Calendar'[Date].[Date], -1, MONTH)

 

The problem is that you CANNOT use WEEK instead of MONTH. WEEK is not supported.

 

So what do I need to put there to get WoW calculation?


DZ

Need Help

12 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    9 years ago

    Why not use DATEADD with DAY and -7?

    • danzrust's avatar
      danzrust
      Helper IV
      9 years ago

      -7 would give you 1 day only... No?

       

      I need to do last 7 days vs last 8-14 days...

       

      DZ

      • Anonymous's avatar
        Anonymous
        Not applicable
        9 years ago

        Hi danzrust

         

        I'm here to help you!

         

        Try this measure out:

         

        Sum of Value this week = CALCULATE(SUM(TABLE[Value]);DATESINPERIOD(TABLE[Date];LASTDATE(TABLE[Date];-7;DAY)

         

        Sum of Value last week = CALCULATE([Sum of Value this week];DATEADD(TABLE[Date];-7;DAY)

         

        Sum of Value this week - Sum of Value last week = [Sum of Value this week] - [Sum of Value last week]

         

        Let me know if this works out. If you need further help, could you possibly provide some test data, whichs simulates your data?

         

        Best,

        Martin

  • delavi's avatar
    delavi
    Frequent Visitor
    8 years ago

    Trying to bump this along since I have been having the same issue.  The closest solution I have gotten is to create the rolling 7 (WoW) in SQL as that is my data source.  Still not what I wanted to do for the sake of using PBI fully.

     

    Also have submitted ticket to Power BI support.

     

    WoW should be an automatic feature for any BI tool.