Cumulative Sales Subtotaling Column by Day over a Week

[strikenoir]

Hi,

Relatively new to Power BI and using DAX, but I've so far accomplished being able to produce everything I wanted to. There is however, one component to the report I am trying to build that is a business requirement towards it.

This is a simple report that is intended to run daily to gives sales and other metrics by day of the week for a given week. While it can return a grand total at the very far right giving the sum of everything, I want to somehow include (somewhere) a section that gives the cumulative total for only the days that have past in the week.

See image below for example of the standard Excel version of how we produce the report now.

I figure a standard matrix in BI is not really able to fully accomplish this, so I don't know how simply or easy it is to achieve this, if at all. If curious why this cumulative total is needed, the sales are also compared against the previous year, so the grand total at the far right would compare only days that have past versus all of last year - not realistic from a business perspective; removing the previous data from days that have yet to come is also not a viable solutions as it is preferred to see the figures still as future comparables.

I appreciate any help, feedback or alternatives that can be provided.

Thanks,

Johnson 

[v-caliao-msft · Accepted solution]

@strikenoir,

Based on your description, you need a function to return week to date value. Currently, there is not such a function that return WTD directly. We can use this DAX expression instead.

Sales-CWeek =
CALCULATE (
    [SalesTotal],
    FILTER (
        'PeriodDate',
        YEAR ( 'PeriodDate'[Date] ) = YEAR ( NOW () )
            && WEEKNUM ( 'PeriodDate'[Date], 2 ) = WEEKNUM ( NOW (), 2 )
    )
)

 And then for last year, you could use this function

Sales-CWeek =
CALCULATE (
    [SalesTotal],
    FILTER (
        'PeriodDate',
        YEAR ( 'PeriodDate'[Date] ) = YEAR ( NOW ()-1 )
            && WEEKNUM ( 'PeriodDate'[Date], 2 ) = WEEKNUM ( NOW (), 2 )
    )
)

Regards,

Charlie Liao