Forum Discussion

SaulM's avatar
SaulM
Frequent Visitor
4 years ago
Solved

Prior Month DAX with Measure

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's avatar
    mahoneypat
    4 years ago

    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

     

2 Replies

  • mahoneypat's avatar
    mahoneypat
    Microsoft Employee
    4 years ago

    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's avatar
      SaulM
      Frequent Visitor
      4 years ago

      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!