Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
What's the best way to create a virtual table that pulls the Customer Code, the Last Sale Date and Total Sales for that customer?
The goal is to generate a list of Customers who have not purchased from us in the last year.
Here's is some sample data:
I need to produce a table in Power BI exactly like the Pivot table shown above. My SUMMARIZECOLUMNS measure produces the result similar to what is shown on the left. I need to just see the Last Sale Date and the total sales for each customer (highlighted rows are what is shown in the pivot table).
I tried using SUMMARIZECOLUMNS, but that didn't work. I tried CALCULATETABLE, but I get an error.
Here is my SUMMARIZECOLUMN measure:
Solved! Go to Solution.
Hi @Roseventura
I think you want to show dynamic result by days slicer. Power BI doesn't support calculated table to show dynamic result based on slicer. I suggest you to create measures. I think you want to calculate the max day before today()-N and sum of Sales before today()-N.
Measures:
MAX of DATE =
VAR _SELECTDAYS = SELECTEDVALUE(Days[Days])
RETURN
CALCULATE(MAX(Shipments[DATE]),FILTER(Shipments,Shipments[DATE]< TODAY()-_SELECTDAYS))
Sum of SALES =
VAR _SELECTDAYS = SELECTEDVALUE(Days[Days])
RETURN
CALCULATE(SUM(Shipments[SALES]),FILTER(Shipments,Shipments[DATE]< TODAY()-_SELECTDAYS))
Result:
Best Regards,
Rico Zhou
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Something like this should work:
SUMMARIZE(Shipments,Shipments[CUSTOMER CODE],"Last Sale",MAX(Shipments[Date]),"Total Sales",SUM([Sales]))
Ibendlin:
I tried that a few days ago, but I need to incorporate the filter shown in BOLD below (which I why I switched from SUMMARIZE to SUMMARIZECOLUMNS.
Hi @Roseventura
I think you want to show dynamic result by days slicer. Power BI doesn't support calculated table to show dynamic result based on slicer. I suggest you to create measures. I think you want to calculate the max day before today()-N and sum of Sales before today()-N.
Measures:
MAX of DATE =
VAR _SELECTDAYS = SELECTEDVALUE(Days[Days])
RETURN
CALCULATE(MAX(Shipments[DATE]),FILTER(Shipments,Shipments[DATE]< TODAY()-_SELECTDAYS))
Sum of SALES =
VAR _SELECTDAYS = SELECTEDVALUE(Days[Days])
RETURN
CALCULATE(SUM(Shipments[SALES]),FILTER(Shipments,Shipments[DATE]< TODAY()-_SELECTDAYS))
Result:
Best Regards,
Rico Zhou
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
109 | |
102 | |
84 | |
79 | |
70 |
User | Count |
---|---|
120 | |
110 | |
95 | |
82 | |
77 |