Cumulative Comparison Between This Week and Last Week in a Single Visual

[drx]

Hi Power BI Community,

I'm working on a report in Power BI where I want to show the cumulative totals for this week and the previous week on the same visual. I'm using DirectQuery mode(so I can't make columns or tables), and here’s the issue I’m encountering:

I can calculate the cumulative totals for this week and the previous week using the following DAX measures:

DAX

CE Orders = SUM(T_Verkooporder[Aantal ce besteld])

CE Orders =
              CALCULATE(
                    [CE Orders],
                           FILTER(
                           ALLSELECTED(DM_Leverdatum),
                           DM_Leverdatum[Datum] <= MAX(DM_Leverdatum[Datum])
                           )
                 )

CE Last week =
             CALCULATE(
                   [CE Orders],
                   DATEADD(DM_Leverdatum[Datum], -7, DAY)
)

CE Last Week Orders =
               CALCULATE(
                   [CE Last week],
                          FILTER(
                                  ALLSELECTED(DM_Leverdatum),
                                  DM_Leverdatum[Datum] <= MAX(DM_Leverdatum[Datum])
                           )
                  )

I'm using ALLSELECTED because my date collumn goes up to 2099 

Currently, I'm using two separate visuals:

• The second visual is filtered for the previous 14 days (to cover the previous week).
• One visual is filtered for the last 7 days (current week).
  

Vorige week meaning last week.
This is what happens when i combine them

The issue I have is that when I try to use both the DATEADD function and filter for the last 7 days, the previous weeks data falls outside the filtered context, and I can't show both cumulative values in one graph.

What I'm trying to achieve:

• I want to display both cumulative values (this week and last week) in a single visual with a day-by-day progression.
• How can I calculate the cumulative for both this week and last week without losing the context for the current week or breaking the DATEADD function?
• If possible also make Monday the start of the week

CE besteld test =
            CALCULATE(
                  [CE besteld],
                          FILTER(
                               DM_Leverdatum,
                               WEEKNUM(DM_Leverdatum[Datum], 2) = WEEKNUM(TODAY(), 2) &&
                               YEAR(DM_Leverdatum[Datum]) = YEAR(TODAY()) &&
                               DM_Leverdatum[Datum] <= TODAY()
                             )
                )


I tried this but it's no longer cumulative when i do this. However, this isn't as important.

Any guidance on how to structure the DAX for this would be greatly appreciated!

Thanks in advance for your help

[v-xinruzhu-msft · Accepted solution]

Hi,

Thanks for the solution amitchandak offered, and i want to offer some more information for user to refer to.

hello @drx , based on your description, you can refer to the following sample.

Sample data 

Calendar table.

There is a 1:n relationship between the tables.

Then for this week  cumulative totals.

CE Orders =
IF (
    SUM ( 'T_Verkooporder'[Aantal ce besteld] ) <> BLANK (),
    CALCULATE (
        SUM ( 'T_Verkooporder'[Aantal ce besteld] ),
        ALLSELECTED ( 'DM_Leverdatum' ),
        'DM_Leverdatum'[Datum] <= MAX ( 'DM_Leverdatum'[Datum] )
    )
)

For last week.

CE Last week = CALCULATE([CE Orders],DATEADD('DM_Leverdatum'[Datum],-7,day))

Then create a measure to control the last week start from Monday.

Flag =
VAR a =
    MAXX ( ALLSELECTED ( 'DM_Leverdatum'[Datum] ), [Datum] )
VAR b =
    a - WEEKDAY ( a, 2 ) + 1
RETURN
    IF (
        SELECTEDVALUE ( 'DM_Leverdatum'[Datum] ) >= b
            && SELECTEDVALUE ( 'DM_Leverdatum'[Datum] ) <= a,
        1,
        0
    )

Then put the following fields in visual.

Then put the falg measure to the visual filter.

Output

Best Regards!

Yolo Zhu

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[amitchandak]

@drx , Create a week rank column in Date/calendar table

Have these new columns in Date Table, Week Rank is Important in Date/Week Table
Week Start date = 'Date'[Date]+-1*WEEKDAY('Date'[Date],2)+1
Week End date = 'Date'[Date]+ 7-1*WEEKDAY('Date'[Date],2)
WeekDay = weekday([Date],2)
Have these new columns in Date Table, Week Rank is Important in Date/Week Table

Week Rank = RANKX('Date','Date'[Week Start date],,ASC,Dense)
OR
Week Rank = RANKX('Date','Date'[Year Week],,ASC,Dense) //YYYYWW format

And measures like

WTD = CALCULATE(sum('Table'[Qty]), FILTER(ALL('Date'),'Date'[Week Rank]=max('Date'[Week Rank]) && 'Date'[WeekDay]<=max('Date'[WeekDay])))
LWTD = CALCULATE(sum('Table'[Qty]), FILTER(ALL('Date'),'Date'[Week Rank]=max('Date'[Week Rank])-1 && 'Date'[WeekDay]<=max('Date'[WeekDay]) ))

Power BI — Week on Week and WTD
https://medium.com/@amitchandak.1978/power-bi-wtd-questions-time-intelligence-4-5-98c30fab69d3
https://community.powerbi.com/t5/Community-Blog/Week-Is-Not-So-Weak-WTD-Last-WTD-and-This-Week-vs-La...
https://www.youtube.com/watch?v=pnAesWxYgJ8

[drx]

I'm using direct querry so i can't create collumns