cancel
Showing results for
Did you mean:
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
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
4 REPLIES 4
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
Helper I

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?

Helper I

Works perfectly! thank you 🙂

Super User

@ING_BT This may help. Specifically SUMIF

S Excel to DAX Translation - Microsoft Power BI Community

Become an expert!: Enterprise DNA
External Tools: MSHGQM
Latest book!:
Learn Power BI 2nd Edition

Announcements