Forum Discussion

RafaelRSantosBR's avatar
RafaelRSantosBR
Regular Visitor
2 years ago

How get last previous date from variable date

Hi, I have a Fact table linked to DimDate. I Need get last date previous month from date passed as measure. eg. Measure: LastDate = 2023-10-25   Dax Var lastDate = LastDate Var vYear = Year(L...
RafaelRSantosBR's avatar
RafaelRSantosBR
Regular Visitor
2 years ago

I make this DAX, but i think have most elegant way to do.


I will wait for responses, thanks

  • sevenhills's avatar
    sevenhills
    Super User
    2 years ago

    Just curious, did you try using EOMONTH and -1.

    I cannot read spanish else would have refined your DAX.

     

    measure1 =
VAR _PrevMonthEndDate = EOMONTH ( LastDate, -1 )
VAR _resultLastTxDate =
    MAXX (
        FILTER ( ALL ( 'Table1'[Date] ), 'Table1'[Date] <= _PrevMonthEndDate),
        'Table1'[Date]
    )
RETURN    _resultLastTxDate

     

    • RafaelRSantosBR's avatar
      RafaelRSantosBR
      Regular Visitor
      2 years ago

      Hi sevenhills , thanks for reply.

      Your DAX return the last day of previous month of Dimension Date Table, and not the last day of previous month of Fact Table.

       

      I made this updates to work:

      measure1 = 
VAR _PrevMonthEndDate = EOMONTH ( [UltimaData], -1 )
VAR _resultLastTxDate =
    MAXX (
        FILTER ( ALL ( 'Publico DimData'[Data] ), 'Publico DimData'[Data] <= _PrevMonthEndDate),
        'Publico DimData'[Data]
    )
RETURN
_resultLastTxDate

      Thanks for help

      • sevenhills's avatar
        sevenhills
        Super User
        2 years ago

        I was not sure whether it worked or not...

         

        Now, I see what you want is previous month max transaction date based on certain colum. I will recommend to try this.

         

        Below is the DAX from Adventure Works DW 2020

        Prev.Month - Max.Sales.Tx.Date - Sales Amount = 
var _PrevMonthStartDate  = EOMONTH ( [Last Sales Tx Date], -2 ) + 1 
var _PrevMonthEndDate    = EOMONTH ( [Last Sales Tx Date], -1 )
var _LastDateInPreviousMonthTx =  LASTNONBLANK ( 
       Filter( all('Date'[Date]), 
            'Date'[Date] >= _PrevMonthStartDate 
            && 'Date'[Date] <= _PrevMonthEndDate)
       , CALCULATE ( SUM ( Sales[Sales Amount] ) )) 
return _LastDateInPreviousMonthTx

         

         

        Similarly, You can do like this by adjusting the fact table column and dim date column.

         

        measure1 = 
var _PrevMonthStartDate  = EOMONTH ( [UltimaData], -2 ) + 1 
var _PrevMonthEndDate    = EOMONTH ( [UltimaData], -1 )
var _LastDateInPreviousMonthTx =  LASTNONBLANK ( 
        Filter( all('Dim Date'[Date]),
             'Dim Date'[Date] >= _PrevMonthStartDate 
             && 'Dim Date'[Date] <= _PrevMonthEndDate)
        , CALCULATE ( SUM ( FactTable[Sales Amount] ) )) 
return _LastDateInPreviousMonthTx