Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
Hello!
I need to generate a formula that brings me the minimum date of a column, minus 3 months.
Generate a calculated measure to bring back the oldest date (MIN). That's what I wanted to apply a Dateadd to, but since it's not a date table, it doesn't allow me to generate the formula.
Thank you very much from now, I hope you have understood what I am looking for.
Best regards.
Solved! Go to Solution.
Hi @Verosb ,
You can create a measure as below to get it, please find the details in the attachment.
3 months before Mindate =
VAR _mindate =
CALCULATE ( MIN ( 'Table'[Date] ), ALLSELECTED ( 'Table' ) )
VAR _premonth =
EOMONTH ( _mindate, -3 )
VAR _minday =
DAY ( _mindate )
VAR _preday =
DAY ( _premonth )
RETURN
IF (
_minday > _preday,
_premonth,
DATE ( YEAR ( _premonth ), MONTH ( _premonth ), DAY ( _mindate ) )
)
Best Regards
Hi @Verosb ,
You can create a measure as below to get it, please find the details in the attachment.
3 months before Mindate =
VAR _mindate =
CALCULATE ( MIN ( 'Table'[Date] ), ALLSELECTED ( 'Table' ) )
VAR _premonth =
EOMONTH ( _mindate, -3 )
VAR _minday =
DAY ( _mindate )
VAR _preday =
DAY ( _premonth )
RETURN
IF (
_minday > _preday,
_premonth,
DATE ( YEAR ( _premonth ), MONTH ( _premonth ), DAY ( _mindate ) )
)
Best Regards
Hi @Syndicate_Admin,
You can use:
Min Date Minus 3 Months = DATE(YEAR(MIN('Table'[Date])),MONTH(MIN('Table'[Date]))-3,DAY(MIN('Table'[Date])))
Works for you? Mark this post as a solution if it does!
Check out this blog of mine: How to Export Telemetry Data from Azure IoT Central into Power BI