Prior Month DAX with Measure

[SaulM]

Hello,

I have been trying to figure out this measure for 3 days and have not gotten anywhere. I have a measure created "Total Exceptions Count" has does a countrows('ExceptionsTable') works with no issues. I also have a "Total Exceptions Recent Month" measure to use to calculate the count for most recent date.

Total Exceptions Recent Month =

CALCULATE( AccCertExceptions[Total Exception Count] ,

FILTER( ALLSELECTED( 'Date' ),

'Date'[Date] = MAXX(ExceptionsTable, ExceptionsTable[Certification Date] ) )) **we post data around 1 1/2 months back** that's why I need the "Recent Month" calculations.

I can add it to a table visual with dates (dates table) and gives the total exceptions in the month. I need a measure that gives me prior month exceptions. I cannot use the standard of:

Total Exceptions Count PM = Calculate ( [Total Exceptions], DATEADD ( 'Date' [Date], -1, MONTH ) )

Total Exceptons Count PM2 = Calculate ( [Total Exceptions], PREVIOUSMONTH ( 'Date'[Date] ) )

These two do give the desired output, with the exception DATEADD SUMS the total and PREVOUSMONTH does not give a total. I have another measure finding the "% Month Change Exc Count" which throws the calculation wrong. Example: for 8/31/2021 the "% Month Change Exc Count" is [Total Exceptions Recent Month]-[Total Exceptions Count PM]/[Total Exceptions Count] :661-1,879)/18879 gives 96%. What I want is (661-567)/567 = -17%. 

I have also tried the below, but gives no total output like PM2.

Total Exception Count PM =
IF( HASONEVALUE( 'Date'[MonthNumber] ),
CALCULATE( [Total Exception Count] ,
DATEADD( 'Date'[Date], -1, MONTH ) ),
BLANK() )

The green % is what I want to display in a KPI.

Is there a better way to do this. I can't use the "normal" = CALCULATE(SUM(InternetSales_USD[SalesAmount_USD]), PREVIOUSMONTH('DateTime'[DateKey])) as my [Total Exception Count] is a measure of COUNTROWS(ExceptionTable) and not a column?

Thanks for your help!

Saul

[mahoneypat · Accepted solution]

Here's one way to do it (with a similar approach to the measure you already have).  I used an existing model to try the approach out.  My Recent Month measure is the same but with 0 instead of -1 in the EOMONTH part.

Total Sales Previous Month =
VAR EOMmaxSalesDate =
    EOMONTH ( MAXX ( Sales, Sales[SaleDate] ), -1 )
RETURN
    CALCULATE (
        [Total Sales],
        FILTER ( ALL ( 'Date'[MonthEnding] ), 'Date'[MonthEnding] = EOMmaxSalesDate )
    )

Pat

[SaulM]

Thanks Pat,

that worked, even works with time inteligence when selecting slicer dates. I guess what I am seeing now is that your measure is giving the MAXX date and it's using that MAX date -1 to calculate previous month and mine was not. Guessing why that worked, right?

I hope you OK from the concussion from this weekends game (Chiefs).

I appreciate the help!! Good start to the week!