Cumulative Sales/ Running Totals not working

[Anonymous]

Hi,

I am trying to create cumulative sales(Running Totals) for a month,

this is the formula I am using to calculate cumulative sales 

# Cumulative Sales =

IF (

MIN ( Dates[Date] ) < TODAY (),

CALCULATE (

Sales[# Sales],

FILTER ( ALLSELECTED ( Dates ), Dates[Date] <= MAX ( Dates[Date] ) )

)

)

The formula is giving me the right results, But as you can see in the below image, when a single date is selected it is only incuding the sales of that day and not counting the previous sales

Not sure why it is doing it, if anybody encountered this before or is there any better way to calculate the cumulative sales?

Any help is much appreciated.

Many Thanks,

Prime

[Mariusz]

Hi @Anonymous 

By using ALLSELECTED ( Dates ) in your filter expression, you are preserving filters coming from outside the visual therefore by selecting a date in the top visual you still apply the filters in your measure, try using ALL( Dates ) instead.

Best Regards,
Mariusz

If this post helps, then please consider Accepting it as the solution.

Please feel free to connect with me.

[Anonymous]

Hi @Mariusz ,

I have tried using All(dates), but it is giving me all the sales, i.e. even are filtering to use only one date, it is showing all the sales.

Regards,

Prime

[Mariusz]

Hi @Anonymous 

What is the outcome that you are expecting?

Best Regards,
Mariusz

If this post helps, then please consider Accepting it as the solution.

Please feel free to connect with me.

[Anonymous]

Hi @Mariusz ,

As shown in the above picture, when I select the 04/09/2019 from the table the matrix should also reflect the same cumulative sales values which is 19,736, but instead it is showing 10,769.

If I dont select any dates the matrix is showing the right values, but when I select any date from the table the matix is showing wrong data.

Thanks in advance.

Regards,

Prime

[v-lid-msft · Accepted solution]

Hi @Anonymous ,

If you want to show a running total of some dates in a table, you can also create the measure using following DAX:

Cumulative Sales = 
SUMX (
    FILTER ( ALL ( Dates ), Dates[Date] <= SELECTEDVALUE ( Dates[Date] ) ),
    [DailySales]
)

BTW, pbix as attached.

Best regards,

Community Support Team _ DongLi
If this post helps, then please consider Accept it as the solution to help the other members find it more

[Mariusz]

Hi @Anonymous 

The below expression is standard practice for Running Totals.

CALCULATE(
    [# Sales],
    FILTER ( 
        ALL( Dates[Date] ), 
        Dates[Date] <= MAX ( Dates[Date] ) 
    )
)

Please let me know if it's working for you.

Best Regards,
Mariusz

If this post helps, then please consider Accepting it as the solution.

Please feel free to connect with me.