Skip to main content
cancel
Turn on suggestions
Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.
Showing results for 
Search instead for 
Did you mean: 

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

Reply
superhayan
Helper I
Helper I

DAX Sumifs Equivalent but with Criteria based on Another Column

‎05-01-2024 04:15 AM

Hello all, I have problem building a DAX measure equivalent to Sumifs in excel. To start with, I know it is to use CALCULATE(SUM(),filter 1, filter 2) but I don't know how to input the filter when it is based on another column.

 

Below is a simplified version of my data - monthly revenue records from a list of clients from 2021-2024. As some are new clients and some are churned, not all the clients have records in the whole period.

ClientRevenue MonthGross Revenue
abc01 September 20232049
abc01 January 202429
qwe01 December 20232
qwe01 October 2023810
qwe01 September 20237665
asd01 December 2021576
asd01 November 2022470
asd01 October 2022859
asd01 September 20221371
bnm01 August 2022672
yui01 July 202243
yui01 September 2023200
yui01 January 20241161

 

The SUMIFS I would like to have is to sum up the Revenue amount with below 2 criteria. 

1) Current 24 months of revenue

2) Only from clients that have generated revenue from the same month last year

 

The final output will be a bar chart of Revenue against Month like below:

superhayan_1-1714561907190.png

 

In such case, the Jan 2024 bar will show the sum of last 24 months revenue generated by only the clients that generated revenue in Jan 2023; and the Jul 2023 bar will show the sum of last 24 months revenue generated by only the clients that have generated revenue in Jul 2022.

 

The underlying data will be updated once a month when new month comes in. I managed to fulfill the first criteria with below measure but don't know how to input the second criteria, becuase I am not filtering the date directly but the client list based ont he date. Please could anyone help?

= CALCULATE(SUM([Gross Revenue]), DATESINPERIOD([Revenue Month], MAX([Revenue Month]),-24, MONTH))

 

Thanks a lot!!!

Message 1 of 9
327 Views
0
1 ACCEPTED SOLUTION
lbendlin
Super User
Super User
In response to superhayan

‎05-01-2024 12:04 PM

change

Cumul = 
var d = max('Calendar'[Date])
var c = values('Table'[Client])
var pc = SELECTCOLUMNS(CALCULATETABLE(VALUES('Table'[Client]),'Calendar'[Date]=EDATE(d,-12)),"Client",[Client])
var yoyc = intersect(c,pc)
return CALCULATE(sum('Table'[Gross Revenue]),'Table'[Client] in yoyc,'Calendar'[Date] <=d,'Calendar'[Date]>EDATE(d,-24))

to

Cumul = 
var d = max('Calendar'[Date])
var pc = SELECTCOLUMNS(CALCULATETABLE(VALUES('Table'[Client]),'Calendar'[Date]=EDATE(d,-12)),"Client",[Client])
return CALCULATE(sum('Table'[Gross Revenue]),'Table'[Client] in pc,'Calendar'[Date] <=d,'Calendar'[Date]>EDATE(d,-24))

View solution in original post

Message 8 of 9
254 Views
8 REPLIES 8
lbendlin
Super User
Super User

‎05-01-2024 05:48 AM

Your sample data is insufficient.

 

lbendlin_0-1714567673261.png

Please provide sample data that fully covers your issue.
Please show the expected outcome based on the sample data you provided.

Message 2 of 9
301 Views
0
superhayan
Helper I
Helper I
In response to lbendlin

‎05-01-2024 05:56 AM

Hello, my data has more than 50k rows to cover all the years so the above data is just a simplified version to show how it looks like. How can I provide this data here?

 

The bar chart I posted in the question is the expected outcome. Please let me know what else I should provide, thank you.

Message 3 of 9
298 Views
0
lbendlin
Super User
Super User
In response to superhayan

‎05-01-2024 05:57 AM

none of your sample data matches your criteria. Please provide sample data that fully covers your issue.

Message 4 of 9
297 Views
0
superhayan
Helper I
Helper I
In response to lbendlin

‎05-01-2024 09:16 AM

Sorry for that. I have created another sample data as below with 4 clients and their revenue spread across 2021-2024 (not all the months have revenue from all 4 clients). Would that be fine? Thanks a lot!!

ClientRevenue MonthGross Revenue
abc01 January 20219293
abc01 February 20218766
abc01 March 20214686
abc01 April 20214359
abc01 May 20217045
abc01 June 20212887
abc01 July 20211980
abc01 August 20217095
abc01 September 20211090
abc01 October 20214097
abc01 November 20211025
abc01 December 20215564
abc01 January 20229342
abc01 February 20227857
abc01 March 20224326
abc01 April 20221661
abc01 May 2022568
abc01 June 20227381
abc01 July 20221044
abc01 August 20229313
abc01 September 20223289
abc01 October 20222524
abc01 November 20228067
abc01 December 20225067
abc01 January 2023653
abc01 February 20232506
abc01 March 20237750
abc01 April 20236728
abc01 May 20231409
abc01 June 20231119
abc01 July 20237218
abc01 August 20231080
abc01 September 20237984
abc01 October 20234474
abc01 November 20238752
abc01 December 20237937
abc01 January 20246201
abc01 February 20248056
abc01 March 20248611
abc01 April 20241857
abc01 May 20247792
abc01 June 20249599
abc01 July 2024844
abc01 August 20247523
abc01 September 20242393
abc01 October 20243873
abc01 November 20248115
abc01 December 20243921
def01 January 20233446
def01 February 2023688
def01 March 20232387
def01 April 20237429
def01 May 20238737
def01 June 20236088
def01 July 20239206
def01 August 20235973
def01 September 20234737
def01 October 20234722
def01 November 20233005
def01 December 20235171
ghi01 November 20222255
ghi01 December 20222052
ghi01 January 20233458
ghi01 February 20239694
ghi01 March 20235958
ghi01 April 20232755
ghi01 May 20232830
ghi01 June 20236555
ghi01 July 20231280
ghi01 August 20232806
ghi01 September 20236985
ghi01 October 20235588
ghi01 November 20234413
ghi01 December 20239755
ghi01 January 20243674
ghi01 February 20244631
ghi01 March 20248251
ghi01 April 20241665
ghi01 May 20243965
ghi01 June 20245606
ghi01 July 20245114
ghi01 August 20243951
ghi01 September 20244773
ghi01 October 20249556
ghi01 November 20243399
ghi01 December 20247994
jkl01 April 20236032
jkl01 May 20239406
jkl01 June 20233464
jkl01 July 20233598
jkl01 August 20234894
jkl01 September 20235268
jkl01 October 20239846
jkl01 November 20233213
jkl01 December 20235506
jkl01 January 20246109
jkl01 February 20242709
jkl01 March 20244500
jkl01 April 20244838
jkl01 May 20241039
jkl01 June 20242135
jkl01 July 20243001
jkl01 August 20244263
jkl01 September 20248892
jkl01 October 20242496
jkl01 November 20246810
jkl01 December 20248066
Message 5 of 9
277 Views
0
lbendlin
Super User
Super User
In response to superhayan

‎05-01-2024 10:20 AM

lbendlin_0-1714583928158.png

 

Cumul = 
var d = max('Calendar'[Date])
var c = values('Table'[Client])
var pc = SELECTCOLUMNS(CALCULATETABLE(VALUES('Table'[Client]),'Calendar'[Date]=EDATE(d,-12)),"Client",[Client])
var yoyc = intersect(c,pc)
return CALCULATE(sum('Table'[Gross Revenue]),'Table'[Client] in yoyc,'Calendar'[Date] <=d,'Calendar'[Date]>EDATE(d,-24))

 

cumulative 24 months with 12 month customer match.pbix
Message 6 of 9
272 Views
0
superhayan
Helper I
Helper I
In response to lbendlin

‎05-01-2024 11:29 AM

Thanks very much for your reply with solution but it is not correct unfortunately. 

 

For example, the bar of Dec 2024 should look at the last 24 months revenue generated by the clients who contribute to revenue in Dec 2023. As all 4 clients generate revenue in Dec 2023, that bar should look at the revenue from all 4 clients from Jan 2022 to Dec 2024. The total sum of the Dec 2024 bar should be $418,725. Also, the Cuml chart is missing out Client def (dark blue color) but it should be included in some bars.

 

Please could you help to take a look again? Thanks so much really!!

Message 7 of 9
264 Views
0
lbendlin
Super User
Super User
In response to superhayan

‎05-01-2024 12:04 PM

change

Cumul = 
var d = max('Calendar'[Date])
var c = values('Table'[Client])
var pc = SELECTCOLUMNS(CALCULATETABLE(VALUES('Table'[Client]),'Calendar'[Date]=EDATE(d,-12)),"Client",[Client])
var yoyc = intersect(c,pc)
return CALCULATE(sum('Table'[Gross Revenue]),'Table'[Client] in yoyc,'Calendar'[Date] <=d,'Calendar'[Date]>EDATE(d,-24))

to

Cumul = 
var d = max('Calendar'[Date])
var pc = SELECTCOLUMNS(CALCULATETABLE(VALUES('Table'[Client]),'Calendar'[Date]=EDATE(d,-12)),"Client",[Client])
return CALCULATE(sum('Table'[Gross Revenue]),'Table'[Client] in pc,'Calendar'[Date] <=d,'Calendar'[Date]>EDATE(d,-24))
Message 8 of 9
255 Views
superhayan
Helper I
Helper I
In response to lbendlin

‎05-03-2024 08:35 AM

It works!! Thank you so much!! I have marked this as the solution already 🙂

Message 9 of 9
164 Views
0

Helpful resources

Announcements
LearnSurvey

Fabric certifications survey

Certification feedback opportunity for the community.

PBI_APRIL_CAROUSEL1

Power BI Monthly Update - April 2024

Check out the April 2024 Power BI update to learn about new features.

April Fabric Community Update

Fabric Community Update - April 2024

Find out what's new and trending in the Fabric Community.

View All
Top Solution Authors
View All