DAX

[ManeeshaY_12345]

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.No	Draw_No	Rev	Revision Date	Release date	No of days	explanation
1	0001	A	12/01/2023	null	0	Release date = null, 0
2	0001	B	18/01/2023	12/02/2023	28	12/02/2023 - 12/01/2023
3	0001	C	19/02/2023	12/03/2023	30	12/03/2023- 12/02/2023
4	0002	D	1/03/2023	19/04/2023	0
5	0002	E		1/05/2023	11	1/05/2023 - 19/04/2023
6	0002	F		12/05/2023	12	12/05/2023 - 1/05/2023
	0001	G		12/06/2023	90	12/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.

[Daniel29195 · Accepted solution]

Hello @ManeeshaY_12345 ,

can you please check if this is what you want  : 

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,