Skip to main content
cancel
Showing results for 
Search instead 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

Reply
ThomasSan
Helper IV
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

CustomerSalesDate
1 $          30,0015.06.2020
3 $          12,0001.12.2019
2 $          22,0001.01.2019
1 $            9,0004.08.2020
2 $          25,0015.03.2020
2 $          19,0009.04.2020
1 $          15,0030.09.2020
1 $          24,0004.10.2020
3 $          31,0027.04.2020
2 $            3,0020.08.2020
1 $          32,0030.04.2020

 

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

CustomerBeginning Promotion PhaseEnding Promotion Phase
101.06.202030.06.2020
201.02.202030.04.2020
330.11.201931.01.2020
301.07.202031.10.2020
101.10.202031.12.2020
101.12.201928.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? 

1 ACCEPTED SOLUTION
some_bih
Super User
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

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

 

some_bih_0-1694527914301.png

 

some_bih_1-1694528239265.png

Output

some_bih_2-1694528252249.png

 

 





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!






View solution in original post

10 REPLIES 10
some_bih
Super User
Super User

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!






@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.).

ThomasSan_0-1694585378936.png

 

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

some_bih_0-1694589838039.png

 





Did I answer your question? Mark my post as a solution!

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. 

ThomasSan_0-1694591120228.png

 

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)

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





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!






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

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!






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
Super User
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

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

 

some_bih_0-1694527914301.png

 

some_bih_1-1694528239265.png

Output

some_bih_2-1694528252249.png

 

 





Did I answer your question? Mark my post as a solution!

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

ThomasSan_0-1694534172881.png

 

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

ThomasSan_1-1694534526377.png

 

What might be the reason for this?

Helpful resources

Announcements
Fabcon_Europe_Social_Bogo

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 Carousel June 2024

Power BI Monthly Update - June 2024

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

PBI_Carousel_NL_June

Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.

RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.