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(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))

 

The "IF" formula is just added to place 0's in the cell if the result is 0 for the purpose of averaging.

 

The problem I am facing is that when two or more events are registered on the same day, the same calculation occurs per previous date and the same result is referenced for those two or more cells.

 

I would like it to calculate the timeframe between Event 1 and 2 initially, and then subsequent events registered on the same day should be zero.

 

Example below:

 

 

Hoping someone may be able to please assist with this?

 

Thanks,

 

Mitch

 

Need Help

4 Replies

  • Mitchell92's avatar
    Mitchell92
    Helper II
    3 years ago

    I have added in an index table for the events which may make this calc easier. I'm assuming index just needs to be added to check for an earlier index value per Key, but I can't get it to work unfortunately.

     

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