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
ManeeshaY_12345
New Member

DAX

Hi All,
I have data like below, Duplicates of drw-No, if release date is null, No of days = 0,  if minnimum release date of draw_no having date, No of days  = release date - previous date of release date, if previous date of release date null, No of days = release date- previous date of revision date.

S.NoDraw_NoRevRevision DateRelease dateNo of daysexplanation
10001A12/01/2023null0Release date = null, 0
20001B18/01/202312/02/202328 12/02/2023 - 12/01/2023
30001C19/02/202312/03/20233012/03/2023- 12/02/2023
40002D1/03/202319/04/20230 
50002E 1/05/2023111/05/2023 - 19/04/2023
60002F 12/05/20231212/05/2023 - 1/05/2023
 0001G 12/06/20239012/06/2023 - 12/03/2023

 

How can I write dax formula to get above result, please support me if any know the solution.


Thanks,
Maneesha.

1 ACCEPTED SOLUTION
Daniel29195
Super User
Super User

 

Hello @ManeeshaY_12345 ,

can you please check if this is what you want  : 

Daniel29195_0-1705663863519.png

 

 

Daniel29195_1-1705663878862.png

 

 

nb of days =
VAR draw_nb =  'Table (16)'[Draw_No]

var datasource =
FILTER(
        SUMMARIZE(
            'Table (16)',
            'Table (16)'[Draw_No],
            'Table (16)'[Release date]
        ),
        'Table (16)'[Draw_No] = draw_nb
    )

var min_release_date =
    MINX(
        datasource ,
        'Table (16)'[Release date]
    )

var prev_date_of_release_date =
SELECTCOLUMNS(
OFFSET(
    -1,
    datasource,
    ORDERBY('Table (16)'[Release date] , asc )
),
"@prev_release_date",'Table (16)'[Release date]
)


var prev_date_of_revision_date =
SELECTCOLUMNS(
OFFSET(
    -1,
    datasource,
    ORDERBY('Table (16)'[Release date] , asc )
),
"@prev_rev_date",'Table (16)'[Revision Date]
)


RETURN
SWITCH(
    TRUE(),
    'Table (16)'[Release date] = "null" || ISBLANK('Table (16)'[Release date]) , 0 ,
    not ISBLANK(min_release_date) , 'Table (16)'[Release date] - prev_date_of_release_date,
     ISBLANK(prev_date_of_release_date), 'Table (16)'[Release date] - prev_date_of_revision_date
)
   
let me know if you have any problem or concerns,  i would be happy to help you out. 

best regards, 


View solution in original post

1 REPLY 1
Daniel29195
Super User
Super User

 

Hello @ManeeshaY_12345 ,

can you please check if this is what you want  : 

Daniel29195_0-1705663863519.png

 

 

Daniel29195_1-1705663878862.png

 

 

nb of days =
VAR draw_nb =  'Table (16)'[Draw_No]

var datasource =
FILTER(
        SUMMARIZE(
            'Table (16)',
            'Table (16)'[Draw_No],
            'Table (16)'[Release date]
        ),
        'Table (16)'[Draw_No] = draw_nb
    )

var min_release_date =
    MINX(
        datasource ,
        'Table (16)'[Release date]
    )

var prev_date_of_release_date =
SELECTCOLUMNS(
OFFSET(
    -1,
    datasource,
    ORDERBY('Table (16)'[Release date] , asc )
),
"@prev_release_date",'Table (16)'[Release date]
)


var prev_date_of_revision_date =
SELECTCOLUMNS(
OFFSET(
    -1,
    datasource,
    ORDERBY('Table (16)'[Release date] , asc )
),
"@prev_rev_date",'Table (16)'[Revision Date]
)


RETURN
SWITCH(
    TRUE(),
    'Table (16)'[Release date] = "null" || ISBLANK('Table (16)'[Release date]) , 0 ,
    not ISBLANK(min_release_date) , 'Table (16)'[Release date] - prev_date_of_release_date,
     ISBLANK(prev_date_of_release_date), 'Table (16)'[Release date] - prev_date_of_revision_date
)
   
let me know if you have any problem or concerns,  i would be happy to help you out. 

best regards, 


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