Reg: Adding Dates

[gladies123]

Hi Team,

This is to inform you that i am having different two date one is refresh date another one is id_expiry date, if i select a date from slicer which contains refresh_date for example date is (29-06-2021) that id_expiry date should return from (01-06-2021 to 30-6-2021)=this month, and (01-07-2021 to 31-07-2021)=next month, (01-06-2021 to 31-07-2021)=60 days and (01-06-2021 to 31-08-2021)=90 days . Kindly help me on this dax.

Thanks,

Gladis

[v-kelly-msft · Accepted solution]

Hi @gladies123 ,

First create a calendar table as the slicer;

Then create 4 measures as below:

id_expiry date(this month) =
VAR _startdate =
    STARTOFMONTH ( 'Table 2'[Date] )
VAR _enddate =
    ENDOFMONTH ( 'Table 2'[Date] )
RETURN
    IF (
        SELECTEDVALUE ( 'Table 2'[Date] ) IN FILTERS ( 'Table'[refresh date] ),
        FORMAT ( _startdate, "dd-mm-yyyy" ) & " to "
            & FORMAT ( _enddate, "dd-mm-yyyy" )
    )

id_expiry date(next month) =
VAR _startdate =
    DATEADD ( STARTOFMONTH ( 'Table 2'[Date] ), 1, MONTH )
VAR _enddate =
    DATEADD ( ENDOFMONTH ( 'Table 2'[Date] ), 1, MONTH )
RETURN
    IF (
        SELECTEDVALUE ( 'Table 2'[Date] ) IN FILTERS ( 'Table'[refresh date] ),
        FORMAT ( _startdate, "dd-mm-yyyy" ) & " to "
            & FORMAT ( _enddate, "dd-mm-yyyy" )
    )

id_expiry date(60 days) =
VAR _startdate =
    STARTOFMONTH ( 'Table 2'[Date] )
VAR _enddate =
    DATEADD ( ENDOFMONTH ( 'Table 2'[Date] ), 1, MONTH )
RETURN
    IF (
        SELECTEDVALUE ( 'Table 2'[Date] ) IN FILTERS ( 'Table'[refresh date] ),
        FORMAT ( _startdate, "dd-mm-yyyy" ) & " to "
            & FORMAT ( _enddate, "dd-mm-yyyy" )
    )

id_expiry date(90 days) =
VAR _startdate =
    STARTOFMONTH ( 'Table 2'[Date] )
VAR _enddate =
    DATEADD ( ENDOFMONTH ( 'Table 2'[Date] ), 2, MONTH )
RETURN
    IF (
        SELECTEDVALUE ( 'Table 2'[Date] ) IN FILTERS ( 'Table'[refresh date] ),
        FORMAT ( _startdate, "dd-mm-yyyy" ) & " to "
            & FORMAT ( _enddate, "dd-mm-yyyy" )
    )

And you will see:

For the related .pbix file,pls see attached.

Best Regards,
Kelly

Did I answer your question? Mark my reply as a solution!

[amitchandak]

@gladies123 ,The information you have provided is not making the problem clear to me. Can you please explain with an example.

Appreciate your Kudos.