Forum Discussion

JRF_PowerBI's avatar
JRF_PowerBI
Frequent Visitor
3 years ago
Solved

Struggling with SUMMARIZE...

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!

Need Help
  • Anonymous's avatar
    Anonymous
    3 years ago

    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

5 Replies

  • littlemojopuppy's avatar
    littlemojopuppy
    Icon for Community Champion rankCommunity Champion
    3 years ago

    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's avatar
      JRF_PowerBI
      Frequent Visitor
      3 years ago

      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's avatar
        Anonymous
        Not applicable
        3 years ago

        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