Sum rows from the first date until the max selected date in a slicer

[EdK99]

Hello,

I've been searching high and low for a solution for this and I still can't find a way to make it work.

I have a table that contains sales values, the columns are as follow:

Sales Person

Date

Sales Amount

and I have a slicer with dates that are coming from a date table.

My goal is to create a measure that sums up ALL the sales amount until the max selected date. so for example, if the user selected the dates from the slicer as below:

The measure would sum up all the sales amount from 2020 until the max selected month in 2023

Any support would be appreciated, and I am happy to answer any quesitons to clarify the issue.

[jdbuchanan71]

Yes, but you will have to add a dates table to your model.  Then you use the dates table in your slicer and you can write the measure like this.

Total To Date = 
VAR _Max = MAX ( DATES[Date] )
RETURN
CALCULATE ( SUM ( YourTable[Amount] ), DATES[Date] <= _Max )

[jdbuchanan71]

I am assuming you are going to want to have a filter on sales person still work so try a filter like this.

Total To Date = 
VAR _Max = MAX ( YourTable[Date] )
RETURN
CALCULATE ( SUM ( YourTable[Amount] ), ALLEXCEPT ( YourTable, YourTable[Sales Person] ),  YourTable[Date] <= _Max )

[EdK99]

is there any other way of doing this? using ALLEXFCEPT prevents me from adding data from other tables to the visuals.

the visual I'm using is a matrix, it pulls two rows from dimensions tables that are connect to all the other tables, the values in the matrix are the measures and one of the measures is the sales amount. If I use ALLEXCEPT, I can't use data from other tables in the visual.