Calculating weekly running sum

[mkaol]

Hello,

my semantic model has the following structure:

In the fact table FACT_REVENUE, for each snapshot_date/departure_date combination there is a revenue value. It has full revenue value only for Sunday snapshot dates (sunday_delta = 'sunday'). Snapshot dates for the rest of the week (sunday_delta = 'delta') are deltas to the previous day. E.g., the revenue for Wednesday, is a sum of revenue for all snapshot_dates since last Sunday to Wednesday (including Wednesday) for a certain departure_date.

Dimension tables DIM_SNAPSHOT_CALENDAR and DIM_DEPARTURE_CALENDAR contain all snapshot_dates and all departure_dates, accordingly.

I am using the following formula to calculate the weekly running sum of revenue:

revenue_running_sum = 
VAR maximus =
    MAX ( dim_snapshot_calendar[snapshot_date] )
VAR totalSplit =
    CALCULATE (
        SUM ( fact_revenue[revenue] ),
        dim_snapshot_calendar[snapshot_date] <= maximus,
        dim_snapshot_calendar[snapshot_date] >= maximus + 1 - WEEKDAY ( maximus - 7 )
    )
RETURN
    SUMX (
        ADDCOLUMNS (
            VALUES ( dim_snapshot_calendar[snapshot_date] ),
            "@Value", 0
        ),
        totalSplit
    )

First maximum selected snapsh-date is obtained (maximums). The sum is calculated for snapshot_dates <= maximus and snapshot_dates >= last Sunday.

When filters on DIM_SNAPSHOT_CALENDAR[snapshot_date] and DIM_DEPARTURE_CALENDAR[departure_date] are applied, the results are correct:

However, if I split the result by FACT_REVENUE[days_before_departure] (it is a difference between departure_date and snapshot_date), the results are split into each day since last Sunday:

It seems to happen, because when calculating the running sum these snapshot_date/departure_date combinations are selected and when a filter from FACT_REVENUE table is in scope, then it is dispayed as split into those snapshot_date/departure_date combinations.

The expected result is:

Thus, the sum should be displayed for days_before_departure = 1/31/2024 - 2/7/2024 = -7.

Would appreciate any help or suggestions.

Thank you

[mkaol]

Hi Wilson,

Thank you for your response.

Adding week number to the table seems like a good idea. However, unfortunately, I could achieve correct result using this approach. I have tried to:

1. filter SNAP_DT <= MAX(dim_snapshot_calendar[SNAP_DT])
2. calculate sum aggregated by snap week number

For every selected snap_dt.

I have tried some approaches, for example:

fact_revenue[NN YQ CY v2 snap_week] = 
VAR maximus = MAX ( dim_snapshot_calendar[SNAP_DT] ) 
VAR totalSplit = CALCULATE ( SUM ( fact_revenue[revenue] ), 
dim_snapshot_calendar[SNAP_DT] <= maximus ) 

return 
SUMX( 
SUMMARIZE ( fact_revenue_v2, 
dim_snapshot_calendar[SNAP_WEEK_NUM] ) 
,totalSplit) 

It returns only the value for the selected SNAP_DT and not the sum of all SNAP_DTs within the same SNAP_WEEK_NUM, where SNAP_DT <= selected SNAP_DT.

If I were to achieve simillar results using SQL, it would be:

SELECT fact_revenue[snap_week_num], SUM(fact_revenue[revenue]) 
FROM fact_revenue 
WHERE fact_revenue[snapshot_date] > '2024-02-07' 
GROUP BY fact_revenue[snap_week_num] 
; 

Could you maybe tell me, how to build an analogous DAX measure?

Thank you

[Wilson_]

mkaol,

Can you please share a sample pbix file? (If you don't know how, please check the pinned thread in the forum.) It would make debugging your issue easier. 🙂

[mkaol]

Hi Wilson,

I have prepared a pbix file recreateing the Lakehouse and Semantic Model built in Fabric. I have uploaded the file to wetransfer: https://we.tl/t-HcbGv4nD2m. Please let me know, if the link is expired.

Thank you

[Wilson_]

Hi mkaol,

The simplest solution is to make sure your date table also has a week number column. Standard DAX is notoriously cumbersome with a lot of time intelligence functionality that becomes a lot simpler once you incorporate more columns into your date table.

Check out Brian Julius' awesome LinkedIn post for more details.

And of course, if you still need more help, please feel free to let me know where you're stuck and I'd be more than happy to assist. 😄

----------------------------------
If this post helps, please consider accepting it as the solution to help other members find it quickly. Also, don't forget to hit that thumbs up and subscribe! (Oh, uh, wrong platform?)

P.S. Need a more in-depth consultation for your Power BI data modeling or DAX issues? Feel free to hire me on Upwork or DM me directly on here! I would love to clear up your Power BI headaches.