Setting the Date to a different date value

[Anonymous]

Hello there,

I am trying to subtract values of a single column from different dates.

I have created a column (Red Date) which is the current date less 3 months.

Now I want to subtract values from the Red Date from the values at the Main Date.

Acumm(104days) = CALCULATE(
    
    VAR _CurrentCumCases = SUM(Sheet1[Cummulative Cases])
    VAR _OldCumCases =CALCULATE(
        SUM(Sheet1[Cummulative Cases]),FILTER(Sheet1,Sheet1[Date] = Sheet1[RedDate])
)
VAR Red_Population = _CurrentCumCases - _OldCumCases
RETURN
Red_Population
)

At the moment if I return _OldCumCases I get null values and not sure why.

Thanks in advance

[dedelman_clng]

Hi @Anonymous -

FILTER(Sheet1,Sheet1[Date] = Sheet1[RedDate])

FILTER is a table iterator, so you're basically saying "give me all records where Date = Red Date", which is going to be 0 records every time.

Try something like this (not tested since I don't have your data).  You basically need to build a date table that is 3 months shifted from the current one.

Acumm(104days) = CALCULATE(
    
    VAR _CurrentCumCases = SUM(Sheet1[Cummulative Cases])
    VAR _OldCumCases = CALCULATE(
        SUM(Sheet1[Cummulative Cases]),
        DATESBETWEEN( DATEADD ( MIN(Sheet1[Date]) ), -3, MONTH ), 
                      DATEADD ( MAX(Sheet1[Date]) ), -3, MONTH )
)
VAR Red_Population = _CurrentCumCases - _OldCumCases
RETURN
Red_Population
)

In all honesty this may not work as you want without a calendar table if your Sheet1 doesn't have all consecutive dates.  If this doesn't help, please give as much information as you can (sample report with sensitive data removed is always best) and we'll see if we can get you where you need to go.

Hope this helps

David