Add new column based on current row values, using multiple filters

Anonymous — Tue, 29 Sep 2020 21:10:12 GMT

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.

https://community.powerbi.com/t5/Desktop/Add-new-column-with-filters-based-on-current-row-values/m-p/1329206

Last Month Value =

VAR LastMonth = 'PBI Risk Project'[Dates.LastMonth]

RETURN

CALCULATE(

SUM('PBI Risk Project'[Value]),

FILTER(ALL('PBI Risk Project'),

'PBI Risk Project'[Dates.Month]=LastMonth ))

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

Re: Add new column based on current row values, using multiple filters

mahoneypat — Tue, 29 Sep 2020 21:31:02 GMT

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

Re: Add new column based on current row values, using multiple filters

Anonymous — Wed, 30 Sep 2020 08:14:29 GMT

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?

topic Re: Add new column based on current row values, using multiple filters in DAX Commands and Tips

Add new column based on current row values, using multiple filters

Re: Add new column based on current row values, using multiple filters

Re: Add new column based on current row values, using multiple filters