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

Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.

Reply
leodm
Frequent Visitor

DAX Measure to calculate grouped intervals?

Database sample:

ID                   Data de início real                   Squad                   
ID00103/01/2022 00:00:00Squad1
ID00404/01/2022 00:00:00Squad1
ID00706/01/2022 12:00:00Squad1
ID00203/01/2022 14:00:00Squad2
ID00807/01/2022 23:00:00Squad2
ID00907/01/2022 23:00:00Squad2
ID01008/01/2022 03:21:06Squad2
ID01109/01/2022 05:00:00Squad2
ID00303/01/2022 22:30:00Squad3
ID00505/01/2022 23:00:00Squad3
ID00605/01/2022 23:00:00Squad3



The DAX Measure must return the interval of the dates grouped by "Squad".
Must return zero on first Squad row & when there's no interval between two dates.


ID                   Data de início real                   Squad                   Interval (Days)                   
ID00103/01/2022 00:00:00Squad10,00
ID00404/01/2022 00:00:00Squad11,00
ID00706/01/2022 12:00:00Squad12,50
ID00203/01/2022 14:00:00Squad20,00
ID00807/01/2022 23:00:00Squad24,38
ID00907/01/2022 23:00:00Squad20,00
ID01008/01/2022 03:21:06Squad20,18
ID01109/01/2022 05:00:00Squad21,07
ID00303/01/2022 22:30:00Squad30,00
ID00505/01/2022 23:00:00Squad32,02
ID00605/01/2022 23:00:00Squad30,00


I appreciate so much you help.

1 ACCEPTED SOLUTION
wdx223_Daniel
Super User
Super User

wdx223_Daniel_0-1680149326410.png

Interval(Days) =
VAR _pre =
    CALCULATE (
        MAX ( 'TestTable'[Data de início real] ),
        OFFSET (
            -1,
            ALLSELECTED (
                'TestTable'[Data de início real],
                TestTable[Squad],
                TestTable[ID]
            ),
            ORDERBY ( 'TestTable'[Data de início real] ),
            ,
            PARTITIONBY ( 'TestTable'[Squad] )
        )
    )
RETURN
    IF ( _pre, VALUE ( MAX ( 'TestTable'[Data de início real] ) - _pre ), 0 )

 

View solution in original post

1 REPLY 1
wdx223_Daniel
Super User
Super User

wdx223_Daniel_0-1680149326410.png

Interval(Days) =
VAR _pre =
    CALCULATE (
        MAX ( 'TestTable'[Data de início real] ),
        OFFSET (
            -1,
            ALLSELECTED (
                'TestTable'[Data de início real],
                TestTable[Squad],
                TestTable[ID]
            ),
            ORDERBY ( 'TestTable'[Data de início real] ),
            ,
            PARTITIONBY ( 'TestTable'[Squad] )
        )
    )
RETURN
    IF ( _pre, VALUE ( MAX ( 'TestTable'[Data de início real] ) - _pre ), 0 )

 

Helpful resources

Announcements
Microsoft Fabric Learn Together

Microsoft Fabric Learn Together

Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City

PBI_APRIL_CAROUSEL1

Power BI Monthly Update - April 2024

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

April Fabric Community Update

Fabric Community Update - April 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors