Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and a 50 percent discount on exams.
Get startedEarn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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.
Solved! Go to 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.
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.
Hi @v-rongtiep-msft 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.
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.
I have tried with the DAX "PREVIOUSMONTH", but I understand that this is for calculations only 😞
User | Count |
---|---|
91 | |
77 | |
71 | |
64 | |
58 |
User | Count |
---|---|
106 | |
97 | |
76 | |
63 | |
60 |