Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
3 years ago
Solved

Sum based on conditional date range

Hi All,   Struggling with a measure or even column to calculate my desired results, I suspect my required filters might be getting in the way but can't find a workaround.   Current Layout: I've ...
Need Help
  • v-chenwuz-msft's avatar
    v-chenwuz-msft
    3 years ago

    Hi Anonymous ,

     

    Try this :

    datediff =
VAR _F =
    MIN ( 'Table'[EffectiveFrom] )
VAR _T =
    IF (
        ISBLANK ( MAX ( 'Table'[EffectiveTo] ) ),
        TODAY (),
        MAX ( 'Table'[EffectiveTo] )
    )
VAR _RANGES =
    MIN ( 'date table'[Date] )
VAR _RANGEE =
    MAX ( 'date table'[Date] )
VAR _DATE_s =
    IF ( _F > _RANGES, _F, _RANGES )
VAR _DATE_e =
    IF ( _T < _RANGEE, _T, _RANGEE )
RETURN
    IF ( _DATE_e < _DATE_s, 0, DATEDIFF ( _DATE_s, _DATE_e, DAY ) )

    Result:

    datediff* sum('table'[daily fee])

     

     

     

    Pbix in the end you can refer.

    Best Regards

    Community Support Team _ chenwu zhu

     

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

v-chenwuz-msft's avatar
v-chenwuz-msft
Community Support
3 years ago

Hi Anonymous ,

 

Try this :

datediff =
VAR _F =
    MIN ( 'Table'[EffectiveFrom] )
VAR _T =
    IF (
        ISBLANK ( MAX ( 'Table'[EffectiveTo] ) ),
        TODAY (),
        MAX ( 'Table'[EffectiveTo] )
    )
VAR _RANGES =
    MIN ( 'date table'[Date] )
VAR _RANGEE =
    MAX ( 'date table'[Date] )
VAR _DATE_s =
    IF ( _F > _RANGES, _F, _RANGES )
VAR _DATE_e =
    IF ( _T < _RANGEE, _T, _RANGEE )
RETURN
    IF ( _DATE_e < _DATE_s, 0, DATEDIFF ( _DATE_s, _DATE_e, DAY ) )

Result:

datediff* sum('table'[daily fee])

 

 

 

Pbix in the end you can refer.

Best Regards

Community Support Team _ chenwu zhu

 

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

Anonymous's avatar
Anonymous
Not applicable
3 years ago

v-chenwuz-msft I can't thank you enough!

 

I tried rewriting my measure countless times, but definitely underestimated what was required to make it behave as flawless as yours is.

I also didn't realise that the function TODAY() was available in measures, so thank you for that too!

😁