- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Printer Friendly Page
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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.
Can anyone therefore please help me finding the right DAX command?
Solved! Go to Solution.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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
Did I answer your question? Mark my post as a solution!
Proud to be a Super User!
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Hi @ThomasSan check relationship Date And Sales, it should be 1:many (Date on 1 side)
Did I answer your question? Mark my post as a solution!
Proud to be a Super User!
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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?
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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
Did I answer your question? Mark my post as a solution!
Proud to be a Super User!
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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)
Did I answer your question? Mark my post as a solution!
Proud to be a Super User!
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
"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.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Hi @ThomasSan file will be at location tonight, earliest.
Did I answer your question? Mark my post as a solution!
Proud to be a Super User!
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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
Did I answer your question? Mark my post as a solution!
Proud to be a Super User!
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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?
Helpful resources
Join us at the Microsoft Fabric Community Conference
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount!
Microsoft Fabric Community Conference 2025
Arun Ulag shares exciting details about the Microsoft Fabric Conference 2025, which will be held in Las Vegas, NV.
Subject | Author | Posted | |
---|---|---|---|
07-01-2024 05:05 PM | |||
08-13-2024 09:37 AM | |||
08-25-2024 10:54 AM | |||
07-09-2024 05:38 AM | |||
11-07-2023 11:27 PM |
User | Count |
---|---|
21 | |
18 | |
17 | |
7 | |
5 |
User | Count |
---|---|
32 | |
27 | |
19 | |
13 | |
12 |