Measure to calculate totals

[mmm286b]

Hi,

I have a table with these format:

Date                 Works

06/12/2024       50

05/12/2024       42

04/12/2024       13

03/12/2024       50

02/12/2024       11

24/11/2024       13

I've created a parameter with numeric range that indicates the days that I need to substract to calculate how may works are before these day.

For instance, if I select in the slice 5 days. It should be:

Today is 07/12, I substract 5 days -> 02/12. So it should sum until 02/12, so the result would be: 11 (02/12)+ 13(24/11) = 24

I'm trying to do with parameter and a measure. In the measure I put:

Any help please?

[Ashish_Mathur]

Hi,

Try this approach

1. Create a Calendar Table with a relationship (Many to One and Single) from the Date column of the Fact table to the Date column of the Calendar Table
2. Write this measure

Total = sum(Data[Works])

Measure = calculate([Total],datesbetween(calendar[date],minx(all(calendar),calendar[date]),today()-selectedvalue(Days selector[value])))

Hope this helps.

[danextian · Accepted solution]

Hi @mmm286b 

Please try this:

Sum2 = 
VAR _today =
    DATE ( 2024, 12, 7 ) --replace with TODAY()
VAR _daysAgo = _today - [Parameter Value]
RETURN
    CALCULATE (
        SUM ( 'Table'[Works] ),
        KEEPFILTERS ( 'Table'[Date] <= _daysAgo )
    )