Forum Discussion

Mitchell92's avatar
Mitchell92
Helper II
3 years ago

Calculated Column - Need Assistance with formula

Hi all,   I have the below calculated column that works fairly well to calculate timeframe between events in a table based on a "Key":   DaysBetweenEvents = if(VAR temp=TOPN(1,Filter(vwACCCSTWor...
Need Help
Anonymous's avatar
Anonymous
Not applicable
3 years ago

Hi Mitchell92 ,

Please try:

DaysBetweenEvents = 
VAR _index =
    CALCULATE (
        MIN ( 'vwACCCSTWorkload2'[Index] ),
        FILTER (
            ALL ( 'vwACCCSTWorkload2' ),
            'vwACCCSTWorkload2'[Key] = EARLIER ( vwACCCSTWorkload2[Key] )
                && 'vwACCCSTWorkload2'[Date] >= EARLIER ( 'vwACCCSTWorkload2'[Date] )
        )
    )
RETURN
    IF (
        'vwACCCSTWorkload2'[Index] = _index,
        IF (
            VAR temp =
                TOPN (
                    1,
                    FILTER (
                        vwACCCSTWorkload2,
                        vwACCCSTWorkload2[Key] = EARLIER ( vwACCCSTWorkload2[Key] )
                            && 'vwACCCSTWorkload2'[Date] < EARLIER ( vwACCCSTWorkload2[Date] )
                    ),
                    'vwACCCSTWorkload2'[Date], DESC
                )
            RETURN
                DATEDIFF (
                    MINX ( temp, 'vwACCCSTWorkload2'[Date] ),
                    'vwACCCSTWorkload2'[Date],
                    DAY
                ) = 0,
            0,
            VAR temp =
                TOPN (
                    1,
                    FILTER (
                        vwACCCSTWorkload2,
                        vwACCCSTWorkload2[Key] = EARLIER ( vwACCCSTWorkload2[Key] )
                            && 'vwACCCSTWorkload2'[Date] < EARLIER ( vwACCCSTWorkload2[Date] )
                    ),
                    'vwACCCSTWorkload2'[Date], DESC
                )
            RETURN
                DATEDIFF (
                    MINX ( temp, 'vwACCCSTWorkload2'[Date] ),
                    'vwACCCSTWorkload2'[Date],
                    DAY
                )
        )
    )

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

Mitchell92's avatar
Mitchell92
Helper II
3 years ago

Hi Gao,

 

This is 99% perfect, thank you!! One last thing though - how do I make it return a zero if there are no days between events (instead of blanks). This will allow averages to include events with 0 rather than ignore.

 

Thank you!

  • Mitchell92's avatar
    Mitchell92
    Helper II
    3 years ago

    Hi Anonymous ,

     

    I've put the formula into practice now and it seems to work well 90% of the time. I want to thank you initially for that - has helped demonstrate what I am after tremendously.

     

    A problem I have noticed is when an event (in this case, I am using it as my "Index") date is greater than the previous event date, however, a smaller event/index with a later date exists, the formula does not calculate on those events. I am wondering if the formula can be adjusted to essentially calculate differences in date when the event/index has changed? I.e. so it still is 0 for multiple events registering on the same day?

     

    Example below:

     

     

    Thanks,

     

    Mitch