Get all values from date prior to selected on slicer

[Gekko1]

So, I have this table where I got the value of the transaction made on a credit card, the date of the actual sale and the date when the money was liberated to the seller. I want a filter that work like this: when I select a month and a year on slicers, a table gets all the values prior to the last day of that month which are not yet paid i.e. not liberated, which have the pay dates after the selected date.

Value	SaleDate	LiberationDate
50	01/01/2022	01/02/2022
100	01/31/2022	03/01/2022
150	02/25/2022	03/01/2022
200	02/28/2022	03/01/2022
250	03/01/2022	03/02/2022

Accordingly to the table above, if I select the month February it will return the sum of 100, 150 and 200 because, the actual pay date of these sales are made after the last day of the selected month but will not return 250 because the sale wasn't made until the last date of february 2022.

[Gekko1]

Thank you very much, it worked.

[v-yadongf-msft · Accepted solution]

Hi @Gekko1 ,

First, please try following DAX to create a date table:

Table = ADDCOLUMNS(CALENDAR(DATE(2022,1,1),DATE(2022,3,31)),"Month",MONTH([Date]))

Then try following measure:

Measure = 
VAR cur_month  = SELECTEDVALUE('Table'[Month])
VAR cur_value =  CALCULATE(SUM('Sale'[Value]),FILTER(ALL('Sale'), MONTH('Sale'[SaleDate]) <= cur_month && MONTH('Sale'[LiberationDate] ) > cur_month))
return cur_value

The result you want:

Best regards,

Yadong Fang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.