Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register Now

Reply
Mitchell92
Helper I
Helper I

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:

 

Example - BI Forum.PNG

 

Hoping someone may be able to please assist with this?

 

Thanks,

 

Mitch

 

4 REPLIES 4
Mitchell92
Helper I
Helper I

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.

 

Example - BI Forum2.PNG

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

vcgaomsft_0-1671170682163.png

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

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!

Hi @v-cgao-msft ,

 

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:

 

Example - BI Forum3.PNG

 

Thanks,

 

Mitch

Helpful resources

Announcements
Fabric Community Conference

Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

February 2024 Update Carousel

Power BI Monthly Update - February 2024

Check out the February 2024 Power BI update to learn about new features.

Fabric Career Hub

Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

Fabric Partner Community

Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.

Top Solution Authors
Top Kudoed Authors