Starting date in running totals

[LvanLaar]

Hi, I'm looking for a way to do running totals with a starting date.

I have tried looking into the forums and most answers I found had something like:

Running = CALCULATE(

    SUM(Invoices[Amount]),

    FILTER(

        ALL(Calendar[CDate]),

        Calendar[CDate] <= MAX(Calendar[CDate])

    )

)

The problem for me seems to be that I'm not sure how to add a starting date from which to start counting.

For example:

What I'm trying to accomplish is a running total that starts at 0 on the first date in my calendar table (1st of october in this example.) which then adds up untill the 30th of November. It seems however with the code used above my running total starts counting at the start date of my calendar table. Which I think makes sense considering the all filter used.

Some extra info

• The invoice table is connected to my calendar in a many to one relationship.
• On both the slicer, X axis and in my formula I use the date column from my calendar table.

[amitchandak · Accepted solution]

@LvanLaar , Use allselected in place of all

Running = CALCULATE(
SUM(Invoices[Amount]),
FILTER(
allselected (Calendar[CDate]),
Calendar[CDate] <= MAX(Calendar[CDate])
)
)

[LvanLaar]

@amitchandak thank you for your answer. When using all selected I noticed this resolved my problem when using a default date slicer on my visual. However when I tried applying a date hierarchy it reverted to display the same values as when using ALL.

I'm relatively new to Power BI. From my understanding this happens because of the table a date hierarchy creates in the background. I ended up creating a firstday variable outside of my final calculate sum which does seem to have finally solved my problem.

Running =

VAR _FirstDay =

    CALCULATE(FIRSTDATE('Calendar'[CDate].[Date]),

    ALLSELECTED('Calendar'[CDate].[Day])

) RETURN CALCULATE(

    SUM(Invoices[Amount]),

    FILTER(

        ALLSELECTED(Calendar[CDate]),

        'Calendar'[CDate] <= MAX(Calendar[CDate]) &&

        'Calendar'[CDate] >= _FirstDay

    )

)