Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
2 years ago
Solved

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.

General Comment
Interesting Links
Need Help
Show and Tell
Tips and Tricks
Tutorial Requests
  • Daniel29195's avatar
    Daniel29195
    2 years ago

     

    Hello Anonymous ,

    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, 


1 Reply

  • Daniel29195's avatar
    Daniel29195
    Community Champion
    2 years ago

     

    Hello Anonymous ,

    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,