Running Daily for the last 14 days

[ringovski]

Hi All,

From a single table I need to sum the number of rows 'NSW'[cases] for the last 14 days everyday. So each day in the line chart shows the sum for the previous 14 days.

I've tried the following measures;

14 days = CALCULATE(SUM('NSW'[Cases]),

                                    DATESINPERIOD('Date Table'[Date],MAX(NSW[notifcation_date]),14,day)

running total = CALCULATE(SUM('NSW'[Cases]),

                                   FILTER(

                                   ALLSELECTED('NSW'[Notification_date]),

                                  ISONORAFTER('NSW'[Notification_date]), MAX('NSW'[Notification_date])DESC)

I created a cases column with a numeric

Hope this is clear and has enough information.

Thanks for any help

[VahidDM · Accepted solution]

G'day @ringovski 

Try this:

14 days =
VAR _D =
    MAX ( NSW[notifcation_date] )
VAR _14D = _D - 13
RETURN
    CALCULATE (
        SUM ( 'NSW'[Cases] ),
        FILTER (
            ALL ( 'NSW' ),
            NSW[notifcation_date] >= _14D
                && NSW[notifcation_date] <= _D
        )
    )

If this post helps, please consider accepting it as the solution to help the other members find it more quickly.
Appreciate your Kudos!!
LinkedIn: www.linkedin.com/in/vahid-dm/

New Year Power BI eCard:

https://community.powerbi.com/t5/Data-Stories-Gallery/Happy-New-Year/td-p/2266398