Latest Day of Last Month

[Anonymous]

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 

[OwenAuger · Accepted 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

[Anonymous]

Thanks @OwenAuger , that worked perfectly!