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

Question

Hi there&nbsp;I am new to Power BI and I am still learning how to use DAX.&nbsp;I want to add a calculated column which&nbsp;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).&nbsp;ValueIDTypeDateProject NameLast Month DateDates MonthDates LastMonthLast Month Value0KPI 1Red01/09/2020Project A02/08/202020200920200810KPI 2Red01/09/2020Project A02/08/202020200920200820KPI 1Red01/09/2020Project B02/08/202020200920200830KPI 2Red01/09/2020Project C02/08/202020200920200841KPI 1Red10/08/2020Project A11/07/202020200820200702KPI 2Red10/08/2020Project A11/07/202020200820200703KPI 1Red10/08/2020Project B11/07/202020200820200704KPI 2Red10/08/2020Project C11/07/202020200820200700Task 1Blue28/08/2020Project A29/07/202020200820200710000Task 2Blue28/08/2020Project A29/07/202020200820200720000Task 3Blue28/08/2020Project A29/07/202020200820200730000Task 4Blue28/08/2020Project A29/07/202020200820200740001000Task 1Blue28/07/2020Project A28/06/202020200720200602000Task 2Blue28/07/2020Project A28/06/202020200720200603000Task 3Blue28/07/2020Project A28/06/202020200720200604000Task 4Blue28/07/2020Project A28/06/20202020072020060&nbsp;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&nbsp;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 ))&nbsp;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.&nbsp;&nbsp;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 ?&nbsp;Many thanks in advance for your help

mahoneypat · Accepted Answer

Here is a calculated column expression that returns your desired result.&nbsp; FYI that you could also do this as a measure instead.
&nbsp;
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

&nbsp;
Regards,
Pat
&nbsp;

Forum Discussion

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

2 Replies

Related content

Waterfall with multiple values

Text slicer: pasting multiple values

ISFILTERED multiple values

Slicer for Multiple-Choice Values

how to dynamically replace values for multiple columns

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