Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more.
Get startedGrow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.
Hello community
I'm having trouble creating a column that sums up the turnover for each customer created within the first 20 days of his first purchase.
I have added a picture and a google sheets example of the data ( the yellow column is what I'm trying to create).
My dax so far looks like this:
https://docs.google.com/spreadsheets/d/1EQ5-fy2Q66skkIX4aP6RJnltIdeb5OUVYX_SaHg_swg/edit?usp=sharing
Thank you in advance
Solved! Go to Solution.
Hi @AlexanderBP,
Please try this column formula, it results in the following table based on your sample data.
20DaysPurchases =
var custId = [Customer id]
var firstPurchaseDate = CALCULATE(MIN([Customer Added Date]), filter(ALL('Table'), 'Table'[Customer id] = custId))+20
return
CALCULATE(SUM([Turnover]), FILTER(ALL('Table'), 'Table'[Purchase Date] <= firstPurchaseDate && 'Table'[Customer id] = custId))
Hope this Helps,
Richard
Did I answer your question? Mark my post as a solution!
Did my answers help arrive at a solution? Give it a kudos by clicking the Thumbs Up!
Proud to be a Super User!
Hi @AlexanderBP ,
The below column works in your scenario, too. The calculation logic is similar to that of @richbenmintz.
And if you create it as a measure, it also works.
20DaysPurchases =
VAR FirstPurchase =
CALCULATE (
MIN ( 'Table'[Purchase Date] ),
ALLEXCEPT ( 'Table', 'Table'[Customer id] )
)
RETURN
CALCULATE (
SUM ( 'Table'[Turnover] ),
FILTER (
ALLEXCEPT ( 'Table', 'Table'[Customer id] ),
'Table'[Purchase Date] <= FirstPurchase + 20
)
)
You can check more details from here.
Best Regards,
Icey
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi @AlexanderBP ,
The below column works in your scenario, too. The calculation logic is similar to that of @richbenmintz.
And if you create it as a measure, it also works.
20DaysPurchases =
VAR FirstPurchase =
CALCULATE (
MIN ( 'Table'[Purchase Date] ),
ALLEXCEPT ( 'Table', 'Table'[Customer id] )
)
RETURN
CALCULATE (
SUM ( 'Table'[Turnover] ),
FILTER (
ALLEXCEPT ( 'Table', 'Table'[Customer id] ),
'Table'[Purchase Date] <= FirstPurchase + 20
)
)
You can check more details from here.
Best Regards,
Icey
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi @AlexanderBP,
Please try this column formula, it results in the following table based on your sample data.
20DaysPurchases =
var custId = [Customer id]
var firstPurchaseDate = CALCULATE(MIN([Customer Added Date]), filter(ALL('Table'), 'Table'[Customer id] = custId))+20
return
CALCULATE(SUM([Turnover]), FILTER(ALL('Table'), 'Table'[Purchase Date] <= firstPurchaseDate && 'Table'[Customer id] = custId))
Hope this Helps,
Richard
Did I answer your question? Mark my post as a solution!
Did my answers help arrive at a solution? Give it a kudos by clicking the Thumbs Up!
Proud to be a Super User!
@AlexanderBP - I'm not sure I understand what you are trying to do with your DATESBETWEEN statement. Can you explain what you are trying to do in your formula?
You may find this helpful - https://community.powerbi.com/t5/Community-Blog/To-bleep-With-Time-Intelligence/ba-p/1260000
Also, see if my Time Intelligence the Hard Way provides a different way of accomplishing what you are going for.
https://community.powerbi.com/t5/Quick-Measures-Gallery/Time-Intelligence-quot-The-Hard-Way-quot-TIT...
@AlexanderBP , a similar discussion on going on
Check the suggested solutions there
User | Count |
---|---|
86 | |
84 | |
69 | |
67 | |
55 |
User | Count |
---|---|
125 | |
100 | |
90 | |
84 | |
66 |