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(LastDate)

Var vMonth = Month(LastDate)

Var firstdate = Date(vYear, vMonth, 1)

 

The last day of previous month of my fact table is 2023-09-29

 

I need a DAX to return 2023-09-29  instead of 2023-09-30(last day of previous month of DimDate table)

 

Thanks

6 Replies

  • 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

  • EylesIT's avatar
    EylesIT
    Resolver II
    2 years ago

    RafaelRSantosBR, here is my suggested solution.

    Create a measure with this DAX expression. I think you need to query the FactTable, but remove the filters from both the FactTable and the dimDate table.

     

    Last Date in Previous Month from Fact Table = 
    VAR vLastDate = [LastDate]
    VAR vPrevMonth = EDATE(vLastDate, -1)
    VAR vYear = YEAR(vPrevMonth)
    VAR vMonth = MONTH(vPrevMonth)

    RETURN 
        CALCULATE(
            MAX(FactTable[Date]),
            ALL(FactTable),
            ALL(dimDate),
            YEAR(FactTable[Date]) = vYear,
            MONTH(FactTable[Date]) = vMonth
        )

     

     

     

    • RafaelRSantosBR's avatar
      RafaelRSantosBR
      Regular Visitor
      2 years ago

      EylesIT your calculation return the last day of previous month, but not the last day of previous day that exists in fact table.

      I made some updates in your DAX, because the fact table doesn't have date value, just the foreign key of date value. Follows the code:

      Last Date in Previous Month from Fact Table = 
    VAR vLastDate = [UltimaData]
    VAR vPrevMonth = EDATE(vLastDate, -1)
    VAR vYear = YEAR(vPrevMonth)
    VAR vMonth = MONTH(vPrevMonth)

    RETURN 
        CALCULATE(
            MAX('Publico DimData'[Data]),
            ALL('Investimento FatoEvolucaoFundo'),
            ALL('Publico DimData'),
            YEAR('Publico DimData'[Data]) = vYear,
            MONTH('Publico DimData'[Data]) = vMonth
        )

      Follows the model.