Forum Discussion

drx's avatar
drx
Frequent Visitor
1 year ago
Solved

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

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

Need Help
Show and Tell
Tips and Tricks
Tutorial Requests
  • Anonymous's avatar
    Anonymous
    1 year ago

    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.

     

     

3 Replies

  • amitchandak's avatar
    amitchandak
    Icon for Super User rankSuper User
    1 year ago

    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-Last-Week/ba-p/1051123
    https://www.youtube.com/watch?v=pnAesWxYgJ8

    • drx's avatar
      drx
      Frequent Visitor
      1 year ago

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

  • Anonymous's avatar
    Anonymous
    Not applicable
    1 year ago

    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.