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.
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
Solved! Go to 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!
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!
@gladies123 ,The information you have provided is not making the problem clear to me. Can you please explain with an example.
Appreciate your Kudos.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
111 | |
100 | |
80 | |
64 | |
58 |
User | Count |
---|---|
148 | |
111 | |
93 | |
84 | |
66 |