Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

Cumulative sum if date i between 2 dates

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.

 

 

  

Need Help
  • dax's avatar
    dax
    6 years ago

    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.

2 Replies

  • Nathaniel_C's avatar
    Nathaniel_C
    Icon for Community Champion rankCommunity Champion
    6 years ago
    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

  • dax's avatar
    dax
    Icon for Community Support rankCommunity Support
    6 years ago

    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.