Sum based on conditional date range

[Kcloud]

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 got a Table with two date ranges (Effective From and Effective To) the Effective To can be null.

I've added a Custom Column to calculate the total Active days for each row, and where it's null, it uses LocalNow as below:

if [EffectiveTo] is null 
then Duration.Days (DateTime.Date(DateTime.LocalNow()) - [EffectiveFrom]) 
else Duration.Days([EffectiveTo] - [EffectiveFrom])

I've added a Measure as a filter to my visual, displaying only accounts which is/was active within a date range slicer.

Filter = 
VAR _Range_Start =
    MIN ( 'Date'[Date Name] )
VAR _Range_End =
    MAX ( 'Date'[Date Name] )
RETURN
    IF (
        AND (
            MAX ( 'Managed Deposit Amount'[EffectiveFrom] ) >= _Range_Start,
            MAX ( 'Managed Deposit Amount'[EffectiveFrom] ) <= _Range_End
        )
            && OR (
                ISBLANK ( MAX ( 'Managed Deposit Amount'[EffectiveTo] ) ),
                MAX ( 'Managed Deposit Amount'[EffectiveTo] ) > _Range_End
            ),
        1,
        0
    )

Requirements:

I want to sum the value of a column (Daily Fee) multiplied by the number of Active days within the Date Range, for each row.

e.g.

A user sets a Date Range of 1-January-2022 to 20-January-2022

EffectiveFrom		EffectiveTo		ActiveDays		Daily Fee		Fees in Range
20/08/2021		02/02/2022		166		$10		$200
05/01/2022		10/01/2022		5		$20		$100
04/01/2022		null		253		$10		$160

Row 1 is $200 for the full 20 days within the Date Range.

Row 2 is $100 because it was only active for 5 days within the Date Range.

Row 3 is $160 because it was only active for 16 days within the Date Range.

Current Data:

I've tried a handful of measures but nothing seems to be yielding my expected results. 
I've tried using this to hopefully multiply by the Daily Fee, but the results are incredibly wrong:

ActiveDaysInRange = 
VAR _Range_Start =
    MIN ( 'Date'[Date Name] )
VAR _Range_End =
    MAX ( 'Date'[Date Name] )
RETURN
IF(
    ISBLANK(
        [EffectiveTo]),
        DATEDIFF(_Range_End, [EffectiveFrom], DAY),
        IF(
            MAX([EffectiveFrom]) >= _Range_Start && MAX([EffectiveTo]) >= _Range_End,
            DATEDIFF(_Range_Start, _Range_End, DAY),
            DATEDIFF([EffectiveFrom], [EffectiveTo], DAY)
        )
)

Any assistance is greatly appreciated!

[v-chenwuz-msft · Accepted solution]

Hi @Kcloud ,

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.

[Kcloud]

@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!

😁