Calculate sales till date

[Anonymous]

Hi All,

I want to create a measure that calculates Sales from the start of available data in my table till the last date I have selected.

I am currently using the following formula but the issue is it's affected by the slicer or filter min date selected is there any way to bypass this

"

Total Sales Till Max Selected Date =

VAR StartDate = DATE(2022, 7, 1)

VAR MaxSelectedDate = MAX('Date'[Date])

RETURN

CALCULATE(

    SUM(Sales[SLS Qty]),

    FILTER(

        ALL(Sales[Date]),

        Sales[Date]>= StartDate &&

        Sales[Date] <= MaxSelectedDate

    )

)"

[Anonymous]

Hi thanks for your help but i got the solution using formula below

Total Sales Till Max Date =

CALCULATE(

    SUM(Sales[SLS Qty]),

    FILTER(

        ALL(Sales[Date]),

        Sales[Date] <= MAX(Sales[Date])

    ),

    REMOVEFILTERS('Date'[Date])

)

[johnyip]

@Anonymous , for an easy solution, you can try to disable the interaction between the slicer and your visual.

[Anonymous]

Hi,

if i do that i wont be able to select max date

[johnyip]

In this case, you may create a calculated table whose column is the exact value of your available date (using DAX), or if you prefer, you can do it in PowerQuery.

Leave that calculated table / new PowerQuery Table without any relationship to your data.

Then, use that column of your calculated table in your slicer, and change the definition of MaxSelectedDate in your measure.