cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Helper IV

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

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.

1 ACCEPTED SOLUTION
Super User

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

Output

Proud to be a Super User!

10 REPLIES 10
Super User

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

Proud to be a Super User!

Helper IV

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?

Super User

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

Expected output

Proud to be a Super User!

Helper IV

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.

Super User

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)

Proud to be a Super User!

Helper IV

"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.

Super User

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

Proud to be a Super User!

Helper IV

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

Super User

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

Output

Proud to be a Super User!

Helper IV

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?

Announcements

#### Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

#### Power BI Monthly Update - August 2024

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

#### Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

#### Fabric Community Update - August 2024

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

Top Solution Authors
Top Kudoed Authors