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...
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, 


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,