March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early bird discount ends December 31.
Register NowBe one of the first to start using Fabric Databases. View on-demand sessions with database experts and the Microsoft product team to learn just how easy it is to get started. Watch now
I have two tables, one that has mutliple rows for each date a measure was recorded and another that is my date table. The date table is a typical date dimension with 1 row per date from 1 JAN 1900 through to the year 3000. There is a Foreign Key specified from the Fact Measurement.DateKey to the date table.
Example Fact Measurement:
=Fact Measurement= |Cnt | DateKey| |100 |20190808| |110 |20190808| |100 |20190801| |110 |20190801| |22 |20190725| |333 |20190724|
I would like the find the latest day from last month that has a meausurement in DAX. In the example above, the DAX would return 25 JUL 2019. Next month, it would return 8 AUG 2019.
I can't seem to get close to this with my nearest attempt being:
var eo_last_month = EOMONTH(DATE(YEAR(TODAY()),MONTH(TODAY()),DAY(TODAY())),-1) var lm_measure = FILTER(Measurement, 'Date'[Reporting Date] <= eo_last_month) var latest_date = CALCULATE( MAX('Date'[Reporting Date]), lm_measure ) return latest_date
Solved! Go to Solution.
Hi @Anonymous
Something like this should work:
Latest Date Last Month = CALCULATE ( CALCULATE ( MAX ( 'Date'[Reporting Date] ), 'Measurement' ), PARALLELPERIOD ( TREATAS({TODAY()},'Date'[Reporting Date]), -1, MONTH ) )
Regards,
Owen
Hi @Anonymous
Something like this should work:
Latest Date Last Month = CALCULATE ( CALCULATE ( MAX ( 'Date'[Reporting Date] ), 'Measurement' ), PARALLELPERIOD ( TREATAS({TODAY()},'Date'[Reporting Date]), -1, MONTH ) )
Regards,
Owen
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount!
Arun Ulag shares exciting details about the Microsoft Fabric Conference 2025, which will be held in Las Vegas, NV.
User | Count |
---|---|
116 | |
77 | |
58 | |
52 | |
46 |
User | Count |
---|---|
170 | |
117 | |
63 | |
56 | |
51 |