March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early bird discount ends December 31.
Register NowBe one of the first to start using Fabric Databases. View on-demand sessions with database experts and the Microsoft product team to learn just how easy it is to get started. Watch now
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?
Solved! Go to 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
Did I answer your question? Kudos appreciated / accept solution!
Output
Proud to be a Super User!
Hi @ThomasSan check relationship Date And Sales, it should be 1:many (Date on 1 side)
Proud to be a Super User!
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?
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
Proud to be a Super User!
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.
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!
"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.
Hi @ThomasSan file will be at location tonight, earliest.
Proud to be a Super User!
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
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
Did I answer your question? Kudos appreciated / accept solution!
Output
Proud to be a Super User!
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?
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount!
User | Count |
---|---|
25 | |
17 | |
16 | |
12 | |
11 |
User | Count |
---|---|
39 | |
29 | |
27 | |
20 | |
18 |