Skip to main content
cancel
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
JMSNYC
Helper III
Helper III

Sum Based on Conditional Values from Another Table Columns

Hi.

 

I cannot figure out how to sum column C from Table 1 based on the condition that column B values fit the interval (Column I and J)

 

1f2bbfbb7bf8434af81fa67433cef6a8.pngCould anyone help me solve that problem? Everytime I try to either create a new column or a new measure, DAX does not allow me to use the variables I need to implement the maths. 

 

Thank you in advance

1 ACCEPTED SOLUTION
Greg_Deckler
Super User
Super User

@JMSNYC - Should be as a column:

 

Actual Revenue = SUMX(FILTER(RELATEDTABLE('DAILY REVENUES'),[Date Rev.]>=[Tender Start Date] && [Date Rev.]<=[Tender End Date]),[Revenue])

 

If that does not work please @ me

I did confirm this in the attached PBIX file below sig. Table (23), Table (23a), Table (23b)


Follow on LinkedIn
@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
The Definitive Guide to Power Query (M)

DAX is easy, CALCULATE makes DAX hard...

View solution in original post

2 REPLIES 2
amitchandak
Super User
Super User

@JMSNYC , Try a new column like this Tenders Table

 

sumx(filter(DailyRevenue, Tenders[TenderStartDate]<=DailyRevenue[DateRev] 
		&& Tenders[TenderEndtDate]>=DailyRevenue[DateRev] && DailyRevenue[CustomerUID] = Tenders[CustomerUID]),DailyRevenue[Revenue])

 

Greg_Deckler
Super User
Super User

@JMSNYC - Should be as a column:

 

Actual Revenue = SUMX(FILTER(RELATEDTABLE('DAILY REVENUES'),[Date Rev.]>=[Tender Start Date] && [Date Rev.]<=[Tender End Date]),[Revenue])

 

If that does not work please @ me

I did confirm this in the attached PBIX file below sig. Table (23), Table (23a), Table (23b)


Follow on LinkedIn
@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
The Definitive Guide to Power Query (M)

DAX is easy, CALCULATE makes DAX hard...

Helpful resources

Announcements
RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

MayPowerBICarousel

Fabric Monthly Update - May 2024

Check out the May 2024 Fabric update to learn about new features.

LearnSurvey

Fabric certifications survey

Certification feedback opportunity for the community.