cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
ING_BT
Helper I
Helper I

Conditional Sum - Evaluate only after date for each Company

Hello! 

 

I'm trying to create a conditional sum of sales such as: 

 

I have sales data for Company A, B, C and D for the whole 2021 year. However, I have in another table dates from which I should be calculating the Sales such as: 

Company A: calculate only after 10.02.2021

Company B: calculate only after 15.06.2021

Company C: calculate only after 17.08.2021

Company 😧 calculate only after 02.10.2021

 

This table is connected to the Sales tables with Company names, Dates and number of sales

And is connected to the Date table (also connected to sales tables). 

 

How can I create a Sum measure, to ingnore for each companies the Sales before the related dates? 

 

I'm sure the solution is simple, but I can't wrap my head around it! 

 

Thank you so much for your help.

 

Best,

BT. 

1 ACCEPTED SOLUTION
speedramps
Super User
Super User

Consider this solution

 

create a company table with

company id, company name, afterdate

create a sales table with

date, company id, value

 

create a 1:m relationship from company[companyid] to sales[companyid]

 

create measure

 

Aftersales =
VAR myafterdate = SELECTEDVALUE(Company[afterdate])
RETURN
CALCULATE(
SUM(sales[value],
Sales[date] > myafterdate
)
 
Drag company[company name] and [Aftersales] to a table visual
and it will give you the correct answer.
 
Please click thumbbs up and Accept as solution, thank you 

View solution in original post

4 REPLIES 4
speedramps
Super User
Super User

Consider this solution

 

create a company table with

company id, company name, afterdate

create a sales table with

date, company id, value

 

create a 1:m relationship from company[companyid] to sales[companyid]

 

create measure

 

Aftersales =
VAR myafterdate = SELECTEDVALUE(Company[afterdate])
RETURN
CALCULATE(
SUM(sales[value],
Sales[date] > myafterdate
)
 
Drag company[company name] and [Aftersales] to a table visual
and it will give you the correct answer.
 
Please click thumbbs up and Accept as solution, thank you 

However, when I put Company and this measure next to normal sum, it will show me another value as expexted for a company where we count after the date; but the sum of those two will be the same. 
How can we include "sum only after this date" if we look at the total of the measure with a slicer that selects all the companies ? 

I don't know if it is clear?

Works perfectly! thank you 🙂 

Greg_Deckler
Super User
Super User

@ING_BT This may help. Specifically SUMIF

S Excel to DAX Translation - Microsoft Power BI Community


@ me in replies or I'll lose your thread!!!
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Learn Power BI 2nd Edition

Helpful resources

Announcements
Power Platform Conf 2022 768x460.jpg

Join us for Microsoft Power Platform Conference

The first Microsoft-sponsored Power Platform Conference is coming in September. 100+ speakers, 150+ sessions, and what's new and next for Power Platform.

Power BI Dev Camp Session 23 768x460.jpg

Check it Out!

Mark your calendars and join us on Thursday, June 30 at 11a PDT for a great session with Ted Pattison!

June 20 episode 7 with aka link 768x460.jpg

The Power BI Community Show

Join us on June 20 at 11 am PDT when Kim Manis shares the latest on Azure Synapse analytics, the Microsoft Intelligent Data Platform, and notable Power BI Updates from Microsoft Build 2022.

Top Solution Authors
Top Kudoed Authors