Cumulative sum if date i between 2 dates

[Anonymous]

Hi, 

I am struggling with the following case.

I have a datetable and a factstable:

factstable:
Value        from                    to

2             7-10-2019            31-12-2019

2             14-10-2019           01-02-2020

2             21-10-2019            01-02-2020

3             22-10-2019             31-12-2019

 all dates between 7-10 and 14-10 should show value 2 and value between 7-10 and 21-10 should show value 2+2 = 4 
and all dates after 21-10 should show a sum of all values. AND dates after 31-12 and before 01-02-2020 should be the sum of 2 (14-10) + 2 (22-10) = 4 , so since values to 31-12 are already ended. 

To put it more simple: show cumulative values if date is after FROM date and before TO date.

[dax · Accepted solution]

Hi Coan7, 

You could try below measure to see whether it work or not.

Measure 2 =
IF (
    MIN ( 'datetable'[Date] ) > MAXX ( ALL ( factable ), factable[from] ),
    CALCULATE (
        SUM ( factable[value] ),
        FILTER (
            ALL ( factable ),
            MIN ( 'datetable'[Date] ) >= factable[from]
                && MIN ( 'datetable'[Date] ) < factable[to]
        )
    ),
    CALCULATE (
        SUM ( factable[value] ),
        FILTER ( ALL ( factable ), MIN ( 'datetable'[Date] ) >= factable[from] )
    )
)

Best Regards,
Zoe Zhi

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

[Nathaniel_C]

SWITCH( TRUE(), IF(DATE >=  7-10-2019 && DATE <= 31-12-2019,
2, IF(DATE >=  7-10-2019 && DATE <= 31-12-2019,
4, (Final Value if none of this is valid)

Hi @Anonymous ,

Try a SWITCH, just fill in the IF values, and the return value.
Let me know if you have any questions.

If this solves your issues, please mark it as the solution, so that others can find it easily. Kudos are nice too.
Nathaniel