Week Over Week Calculations

[danzrust]

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

[delavi]

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.

[Anonymous]

Why not use DATEADD with DAY and -7?

[danzrust]

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

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

DZ

[Anonymous]

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

[danzrust]

@Anonymous:
Your measures are not "sophisticated enough". I need WoW measure to work with last 7D and last 8-14D from any ANY date

Basically, I need this:

I needs to work exactly like the MoM formula in the first post, the only change is the last 7 days vs last 8-14 days instead of months.

[danzrust]

Anyone?

[Vvelarde]

@danzrust

hi, try with this:

WoW =
VAR Week1 =
    CALCULATE (
        SUM ( Table1[Sales] ),
        DATESINPERIOD ( Table1[Today], SELECTEDVALUE ( Table1[Today] ) - 1; -7, DAY )
    )
VAR Week2 =
    CALCULATE (
        SUM ( Table1[Sales] ),
        DATESINPERIOD ( Table1[Today], SELECTEDVALUE ( Table1[Today] ) - 8; -7, DAY )
    )
RETURN
    DIVIDE ( Week1, Week2 ) - 1

regards

Victor

Lima . Peru

[delavi]

This is almost  perfect for me.  Made a couple small changes due to the way PBI counts days - otherwise this is awesome!

WoW =
VAR Week1 =
    CALCULATE (
        SUM ( Table1[Sales] ),
        DATESINPERIOD ( Table1[Today], SELECTEDVALUE ( Table1[Today] ) - 0; -7, DAY )
    )
VAR Week2 =
    CALCULATE (
        SUM ( Table1[Sales] ),
        DATESINPERIOD ( Table1[Today], SELECTEDVALUE ( Table1[Today] ) - 7; -7, DAY )
    )
RETURN
    DIVIDE ( Week1, Week2 ) - 1

@Vvelarde 

[danzrust]

@Vvelarde,

thank you! Another option I figured it myself long time ago. Abs diff:

Clicks WoW Abs =

(
SUMX(FILTER('AdWords KW Data L2W', RELATED('Calendar'[Last X Weeks])="Last Week-00"),'AdWords KW Data L2W'[Clicks])
-
SUMX(FILTER('AdWords KW Data L2W', RELATED('Calendar'[Last X Weeks])="Last Week-01"),'AdWords KW Data L2W'[Clicks])
)

I have custom calendar:

https://docs.google.com/spreadsheets/d/1-55vUYZrYKduI8tbuK5nk9GXBUc7X7uNMcS1mXRIdoI/edit#gid=0

Link for PBI (free to use):

https://docs.google.com/spreadsheets/d/1-55vUYZrYKduI8tbuK5nk9GXBUc7X7uNMcS1mXRIdoI/export?format=cs...

DZ

[danzrust]

@Vvelarde,

thank you! Another option I figured it myself long time ago. Abs diff:

Clicks WoW Abs =

(
SUMX(FILTER('AdWords KW Data L2W', RELATED('Calendar'[Last X Weeks])="Last Week-00"),'AdWords KW Data L2W'[Clicks])
-
SUMX(FILTER('AdWords KW Data L2W', RELATED('Calendar'[Last X Weeks])="Last Week-01"),'AdWords KW Data L2W'[Clicks])
)

I have custom calendar:

https://docs.google.com/spreadsheets/d/1-55vUYZrYKduI8tbuK5nk9GXBUc7X7uNMcS1mXRIdoI/edit#gid=0

Link for PBI (free to use):

https://docs.google.com/spreadsheets/d/1-55vUYZrYKduI8tbuK5nk9GXBUc7X7uNMcS1mXRIdoI/export?format=cs...

DZ

[danzrust]

Figured it out myself long time ago.

I am using custom calendar table. For absolute diff, this is the formula:

Clicks WoW Abs = (
SUMX(FILTER('AdWords KW Data L2W', RELATED('Calendar'[Last X Weeks])="Last Week-00"),'AdWords KW Data L2W'[Clicks])
-
SUMX(FILTER('AdWords KW Data L2W', RELATED('Calendar'[Last X Weeks])="Last Week-01"),'AdWords KW Data L2W'[Clicks])
)

The calendar is here:

https://docs.google.com/spreadsheets/d/1-55vUYZrYKduI8tbuK5nk9GXBUc7X7uNMcS1mXRIdoI/edit?usp=sharing

Import link for PBI (free to use):
https://docs.google.com/spreadsheets/d/1-55vUYZrYKduI8tbuK5nk9GXBUc7X7uNMcS1mXRIdoI/export?format=cs...

DZ

[danzrust]

Hi @Vvelarde,

I have already figured it myself.

I working with my custom calendar table.

The function is (for absolute diff):

Clicks WoW Abs =
(
SUMX(FILTER('AdWords KW Data L2W', RELATED('Calendar'[Last X Weeks])="Last Week-00"),'AdWords KW Data L2W'[Clicks])
-
SUMX(FILTER('AdWords KW Data L2W', RELATED('Calendar'[Last X Weeks])="Last Week-01"),'AdWords KW Data L2W'[Clicks])
)

Calendar is here (free to use): 
https://docs.google.com/spreadsheets/d/1-55vUYZrYKduI8tbuK5nk9GXBUc7X7uNMcS1mXRIdoI/export?format=cs...

Just import it to PBI as "From Web".