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...
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.