Struggling with SUMMARIZE...

[JRF_PowerBI]

Hi

I'm an occasional Power BI user, so not an expert in some of the more complex DAX functions. I have a dataset - see excerpt below - which is a record of void episodes (a period when a property/asset is empty). I'm trying to create a measure which will give me the total number of days that each Asset in the table has been void/empty within a selected date period. I have a separate Date table; in the Power BI report this allows a user to filter to Month-Year (eg June-2023), with the option of multi-selecting so they could filter to a single month, or many months.

The challenges I'm having with creating this measure are:
- there may be multiple void periods within the filtered date period (see assetID = 1313 in dataset above)

- depending on what is selected in the date filter, the calculation of total days void for any individual void episode could be...
1. the number of days between the EffectiveFrom and the EffectiveTo dates
2. the number of days between the EffectiveFrom and the LASTDATE based on the date filter (e.g. where there is no EffectiveTo date, the number of void days would need to reference the LASTDATE)
3. the number of days between the FIRSTDATE based on the date filter and the EffectiveTo date
4. the number of days between the FIRSTDATE and LASTDATE in the date filter (i.e. if the EffectiveTo date is before the start of the filtered date period; and the EffectiveFrom date is after the last date in the filtered date period or is blank)

By this point my brain is already hurting! I'm thinking some use of the SUMMARIZE function is what I need, to evaluate each row and calculate the correct number of days accordingly...but I don't have much experience with this function.

Any pointers or possible solutions to try would be really appreciated!

[littlemojopuppy]

Hi @JRF_PowerBI 

You don't need the SUMMARIZE function for this.  Try a measure like this

SUMX(
	VALUES(AssetID),
	DATEDIFF(
		EffectiveFrom,
		EffectiveTo,
		DAY
	)
)

Not syntactically perfect but should give you the idea.

[JRF_PowerBI]

Thanks, but when I try to type in this formula it doesn't recognise the EffectiveFrom and EffectiveTo dates...suggesting that these are invalid fields to use in this way in this measure. Does the SUMX measure account for the fact that a number of Asset IDs appear mutliple times in the table (StatusID is the unique ref in the table, not AssetID). This is why I thought I would need the SUMMARIZE function...

[Anonymous · Accepted solution]

Hi @JRF_PowerBI ,

Please try this measure:

DaysVoid = 
SUMX (
    'AssetStatus',
    VAR EffectiveFrom = 'AssetStatus'[EffectiveFromDate]
    VAR EffectiveTo =
        IF (
            ISBLANK ( 'AssetStatus'[EffectiveToDate] ),
            LASTDATE ( 'DateTable'[Date] ),
            'AssetStatus'[EffectiveToDate]
        )
    VAR StartDate =
        MAX ( EffectiveFrom, FIRSTDATE ( 'DateTable'[Date] ) )
    VAR EndDate =
        MIN ( EffectiveTo, LASTDATE ( 'DateTable'[Date] ) )
    RETURN
        IF (
            StartDate <= EndDate
                && StartDate >= FIRSTDATE ( 'DateTable'[Date] )
                && EndDate <= LASTDATE ( 'DateTable'[Date] ),
            DATEDIFF ( StartDate, EndDate, DAY ),
            BLANK ()
        )
)

Best Regards,
Gao
Community Support Team

If there is any post helps, then please consider Accept it as the solution  to help the other members find it more quickly.
If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

How to get your questions answered quickly --  How to provide sample data in the Power BI Forum

[JRF_PowerBI]

Strangely, swapping the LASTDATE and FIRSTDATE functions for MAX and MIN seemed to fix the issue and now it's working. Thanks for your help

[JRF_PowerBI]

Thank you @Anonymous - I thought for all the world that this would work...but alas, it just kept returning zeros. I couldn't work out why, so I started stripping out various parts of the formula to try and isolate which part of the measure was causing it to keep producing zeros and I think I've managed to narrow it down to the LASTDATE function. See screenshot below...I've purposefully stripped down the measure so it's always calculating the DATEDIFF using the LASTDATE. I've even set up a separate measure that returns the last date just to ensure I wasn't going mad! But the DATEDIFF calc is still returning zero for everything...I'm completely baffled. No filters applied on report, page (other than MonthYear filter you can see) or any of the visuals.

Any idea what might be causing this / where I'm going wrong?

P.S. Just to prove that it is capable of giving me some sensible figures once the LASTDATE function isn't part of the SUMX measure...