Sales of customers at a specific date that were considered to be promo customers at this time

[ThomasSan]

Hi,

I am looking for a DAX command that helps me display all sales made by customers that were designated as "in promotion" at a given time period.

I have one table which lists all sales being made per customer

Customer	Sales	Date
1	$          30,00	15.06.2020
3	$          12,00	01.12.2019
2	$          22,00	01.01.2019
1	$            9,00	04.08.2020
2	$          25,00	15.03.2020
2	$          19,00	09.04.2020
1	$          15,00	30.09.2020
1	$          24,00	04.10.2020
3	$          31,00	27.04.2020
2	$            3,00	20.08.2020
1	$          32,00	30.04.2020

and I have a table that containts the information how long the promotion phase of each customer lasted:

Customer	Beginning Promotion Phase	Ending Promotion Phase
1	01.06.2020	30.06.2020
2	01.02.2020	30.04.2020
3	30.11.2019	31.01.2020
3	01.07.2020	31.10.2020
1	01.10.2020	31.12.2020
1	01.12.2019	28.02.2020

As you can see, it is possible for any customer to be "in promotion" multiple times.

For example, by setting the date slicer on my visual to "April 2020 - June 2020", I see that we have made $112 in sales. $49 were sales by "in promotion" customers. 

Can anyone therefore please help me finding the right DAX command? 

[some_bih]

Hi @ThomasSan check relationship Date And Sales, it should be 1:many (Date on 1 side)

[ThomasSan]

@some_bih 

oh, right. I corrected the relationship but the issue regarding the main measure persists (i.e. A single value for column 'Date' in table 'Sales' cannot be determined. This can happen when a measure formula refers to a column that contains many values without specifying an aggregation such as min, max, count, or sum to get a single result.).

Do you know why this may be the case?

[some_bih]

Hi @ThomasSan 

It seems your Date table is not yet Date table. In your table you need to have unique date valus and mark table as Date table in Power BI

Link how to create Date / Calendar table below

https://learn.microsoft.com/en-us/power-bi/guidance/model-date-tables 

Expected output

[ThomasSan]

hi @some_bih 

although I marked it as date table now, the problem appears to be in connection with the measure. 

Is it possible to share your pbix file so I can investigate this further? The problem seems to be that Sheet1[Date]/Sales[Date] alone does not yield a single value although a single value is required here. I am therefore wondering why this error message does not appear in your solution.

[some_bih]

Hi @ThomasSan it must be something as as you wrote about Date table.

Possible solutions:

I created Date Table with Bravo for Power BI (I thought you already know how to create Calendar / Date table)

Link for Bravo external tool https://bravo.bi/  (after instalation, connect file and choose Manage dates; delete your current Date table if you wish this solution)

How to share files, the best if you have 365 account to share link (I can see it later tonight earliest)

https://community.fabric.microsoft.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-... 

[ThomasSan]

@some_bih 

"it must be something as as you wrote about Date table."

No, I am acutally refering to Sheet1[Date] (or Sales[Date] in my case) aspects of the measure. The way you created this measure leads, Sheet2 being filtered so that there are only entries left whose Beginning Promotion Phase dates are on the sames day of the sales date column or earlier. But PowerBI does not know how to make this "on the same day or earlier" comparison with an entire column. It has to be a single value. Therefore, the problem is not with the date table itself but with Sheet1[date] representing an entire column. I find it therefore confusing how you managed to have Sheet1[date] here without triggering an error. 

Kindly share your file with me so I can figure this out. You can use this link to do so.

[some_bih]

Hi @ThomasSan file will be at location tonight, earliest.

[ThomasSan]

Hi @some_bih 

thank you for uploading the file. I looked into it and saw that the DAX with which I had problems turned out to be a calculated column, not a measure. It makes sense now. Thank you for the solution

[some_bih · Accepted solution]

Hi @ThomasSan possible solution as following

After import 2 tables, using Power Query from "sales table data" create distinct customer column, and connect it as following. In the same time create Calendar / Date table. 

In "Sales" table create column test with code below

Test =

CALCULATE([Sum sales],

FILTER(Sheet2,Sheet1[Date]>=Sheet2[Beginning Promotion Phase] &&

Sheet1[Date]<=Sheet2[Ending Promotion Phase])

)

Sum sales = SUM(Sheet1[Sales]) is measure

On the outuput below there is April 19 and June 30 totaling 49 as in your example (simple column test in visual),  and second column in visual is measure Sum sales for same rows totaling 82+30=112

Did I answer your question? Kudos appreciated / accept solution!

Output

[ThomasSan]

Thank you for your reply, @some_bih 

I am having difficulties regarding the main measure. When I do it as you did, it returns an error

I created the data model in the same way you had done

What might be the reason for this?