Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hi there
I am new to Power BI and I am still learning how to use DAX.
I want to add a calculated column which uses multiple filters based on a value taken from the current column. My purpose is to look up current months value and add a column with the last months value for that row (I would like to create the column in red below).
Value | ID | Type | Date | Project Name | Last Month Date | Dates Month | Dates LastMonth | Last Month Value |
0 | KPI 1 | Red | 01/09/2020 | Project A | 02/08/2020 | 202009 | 202008 | 1 |
0 | KPI 2 | Red | 01/09/2020 | Project A | 02/08/2020 | 202009 | 202008 | 2 |
0 | KPI 1 | Red | 01/09/2020 | Project B | 02/08/2020 | 202009 | 202008 | 3 |
0 | KPI 2 | Red | 01/09/2020 | Project C | 02/08/2020 | 202009 | 202008 | 4 |
1 | KPI 1 | Red | 10/08/2020 | Project A | 11/07/2020 | 202008 | 202007 | 0 |
2 | KPI 2 | Red | 10/08/2020 | Project A | 11/07/2020 | 202008 | 202007 | 0 |
3 | KPI 1 | Red | 10/08/2020 | Project B | 11/07/2020 | 202008 | 202007 | 0 |
4 | KPI 2 | Red | 10/08/2020 | Project C | 11/07/2020 | 202008 | 202007 | 0 |
0 | Task 1 | Blue | 28/08/2020 | Project A | 29/07/2020 | 202008 | 202007 | 1000 |
0 | Task 2 | Blue | 28/08/2020 | Project A | 29/07/2020 | 202008 | 202007 | 2000 |
0 | Task 3 | Blue | 28/08/2020 | Project A | 29/07/2020 | 202008 | 202007 | 3000 |
0 | Task 4 | Blue | 28/08/2020 | Project A | 29/07/2020 | 202008 | 202007 | 4000 |
1000 | Task 1 | Blue | 28/07/2020 | Project A | 28/06/2020 | 202007 | 202006 | 0 |
2000 | Task 2 | Blue | 28/07/2020 | Project A | 28/06/2020 | 202007 | 202006 | 0 |
3000 | Task 3 | Blue | 28/07/2020 | Project A | 28/06/2020 | 202007 | 202006 | 0 |
4000 | Task 4 | Blue | 28/07/2020 | Project A | 28/06/2020 | 202007 | 202006 | 0 |
I have copied the DAX on the link below as it is similar to my problem.
This does filter my data by the last month but it gives only one total number of the value column for the month on for each row. I need help to filter by Last Month, Project Name and ID.
There is can be multiple IDs of the same name but only one per project per month e.g. Project A only has one KPI 1 for September, one in August and one in July , but there are multiple projects. Is it possible to filter by Last month AND, by Project Name AND by ID for the additional column ?
Many thanks in advance for your help
Solved! Go to Solution.
Here is a calculated column expression that returns your desired result. FYI that you could also do this as a measure instead.
Last Month Value New =
VAR lastmonth = 'PBI Risk Project'[Dates LastMonth]
RETURN
CALCULATE (
SUM ( 'PBI Risk Project'[Value] ),
ALLEXCEPT (
'PBI Risk Project',
'PBI Risk Project'[Project Name],
'PBI Risk Project'[ID]
),
'PBI Risk Project'[Dates Month] = lastmonth
) + 0
Regards,
Pat
To learn more about Power BI, follow me on Twitter or subscribe on YouTube.
Here is a calculated column expression that returns your desired result. FYI that you could also do this as a measure instead.
Last Month Value New =
VAR lastmonth = 'PBI Risk Project'[Dates LastMonth]
RETURN
CALCULATE (
SUM ( 'PBI Risk Project'[Value] ),
ALLEXCEPT (
'PBI Risk Project',
'PBI Risk Project'[Project Name],
'PBI Risk Project'[ID]
),
'PBI Risk Project'[Dates Month] = lastmonth
) + 0
Regards,
Pat
To learn more about Power BI, follow me on Twitter or subscribe on YouTube.
It worked! Thank you very much for your help 🙂
If I use a similar expression in a measure it will work as well or it needs to be written differently?
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
42 | |
21 | |
21 | |
14 | |
13 |
User | Count |
---|---|
43 | |
39 | |
33 | |
18 | |
17 |