How can I get the status of the selected previous month and validate it?

[EduMoA]

Hello team, I need some help with a requirement that I don't know how I can solve in Power BI.

In my data table, I have several columns, of which I need to work with 3 specifically: "KeyID", "BaseDate" and "TypeMonth".

"TypeMonth" is related to my calendar table.

The data "KeyID" can be repeated every month with "BaseDate".

I need to do, is to create 2 columns: (BaseDateLastMonth) and (Comparative)

1. In the "BaseDateLastMonth" column, put the "Base Date" of the previous month selected; for example, if in the filter I select "October" in the column "BaseDateLastMonth" put the "BaseDate" of "September".

If the data is not found in the previous month, enter "NA".

2. In the "Comparative" column I want it to compare "BaseDate" vs "BaseDateLastMonth"; if they are equal, put "TRUE", otherwise "FALSE". 

I attach 3 examples with several cases:

My data:

My Calendar Table:

In the link of drive, I also attach my .pbix file and my datasource.

https://drive.google.com/drive/folders/1bB6ylZwwQ1yfQTJx5hckM3lmf5e4f7r-?usp=sharing

Thanks for your help, team.

Regards.

[Anonymous · Accepted solution]

Hi @EduMoA ,

Please refer to my pbix file to see if it helps you.

Create 2 measures.

BaseDateLastMonth =
VAR _1 =
    CALCULATE (
        MAX ( Data[Custom] ),
        FILTER (
            ALL ( Data ),
            Data[TypeMonth] = SELECTEDVALUE ( Data[TypeMonth] )
                && Data[KeyID] = SELECTEDVALUE ( Data[KeyID] )
        )
    )
VAR _2 =
    EOMONTH ( _1, -1 )
RETURN
    CALCULATE (
        MAX ( Data[BaseDate] ),
        FILTER (
            ALL ( Data ),
            Data[KeyID] = SELECTEDVALUE ( Data[KeyID] )
                && Data[Custom] = _2
        )
    )

Comparative = IF(MAX(Data[BaseDate])=[BaseDateLastMonth],TRUE(),FALSE())

And we can not add "NA" in basedatalastmonth. Because it would change the type of the measure. Then we cannot compare "BaseDate" vs "BaseDateLastMonth".

If it still does not help, please provide more details.

Best Regards

Community Support Team _ Polly

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[EduMoA]

Hi @Anonymous First of all, thanks for the support.

I used the DAX you created and it works for me in the model I shared, but when using it with larger amounts of data, it crashes the dashboard or takes a long time trying to process it.

why do you think this happens?

I had the idea of segmenting the data of the months into different tables, but I don't know if it's ideal.

Regarding the "NA" I understand and agree with that point.

[Anonymous]

Hi @EduMoA ,

"but when using it with larger amounts of data, it crashes the dashboard or takes a long time trying to process it.", You can try the ways  to improve the speed of Power BI:

Optimization guide for Power BI - Power BI | Microsoft Learn

Speed/Performance aspects – The BIccountant

Microsoft Power BI: My Power BI report is slow. Wh... - Microsoft Power BI Community

Microsoft Power BI: The Do?s and Don?ts of Power B... - Microsoft Power BI Community

Best Regards

Community Support Team _ Polly

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[EduMoA]

I have tried with the DAX "PREVIOUSMONTH", but I understand that this is for calculations only 😞