PowerBI DAX: Date periods identification formula issue on previous 3 months

Question

I have a sales data set and want to create a calculated column to be used as legend on graphs and also use as filter of dates but I'm struggling on with the part that identificate the previous 3 months before the last month.

My formula looks like this:

Period identification = 
IF('Base'[Date]=CALCULATE(MAX('Base'[Date]);FILTER('Base';'Base'[FYTD]="FY1920"));"LM";

IF(
    AND('Base'[Date]>=
            DATEADD(CALCULATE(MAX('Base'[Date]);FILTER('Base';'Base'[FYTD]="FY1920"));-3;MONTH);
        'Base'[Date]<CALCULATE(MAX('Base'[Date]);FILTER('Base';'Base'[FYTD]="FY1920")));"P3M";

IF('Base'[Date]=CALCULATE(MAX('Base'[Date]);FILTER('Base';'Base'[FYTD]="FY1819"));"LM YA";"OTHERS")))

The first IF() (Last Month - LM) and the last (Last Month Year Ago - LM YA) are working fine. I just can't find a way of PBI accept the kind of calculus I'm trying to do in the middle to identificate the previous 3 months before the last month.

*The [FYTD] is another calculated column that identificate the corresponding fiscal year of the [Date].[Month]

Thanks in advance

Alexis Olson Alexis Olson · Accepted Answer · 2020-04-02T03:16:55

I think you can make this much easier using the handy EOMONTH function to move through time.

Try something like this where RefDate is the date that you want to use to calculate dates relative to. I'm using the end last month.

Period =
VAR RefDate = EOMONTH ( TODAY (), -1 )
RETURN
    SWITCH (
        TRUE (),
        EOMONTH ( Base[Date], 0 ) = EOMONTH ( RefDate, 0 ), "LM",
        Base[Date] > EOMONTH ( RefDate, -4 ), "P3M",
        EOMONTH ( Base[Date], 0 ) = EOMONTH ( RefDate, -12 ), "LM YA",
        "OTHERS"
    )

Note:SWITCH(TRUE(),...) returns the result for the first condition that evaluates to TRUE().

PowerBI DAX: Date periods identification formula issue on previous 3 months

1 Answers