Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hello everyone, I have done many tries but I am stuck here and could use your help 🙂
I have two tables, one presenting sales of product and another one presenting prediction of sales :
Sales
Category | Colour | day_of_Sales | Sold | ID |
Chair | Blue | 3 | 10 | ChairBlue3 |
Chair | Blue | 2 | 20 | ChairBlue2 |
Chair | Blue | 1 | 10 | ChairBlue1 |
Chair | Red | 2 | 10 | ChairRed2 |
Chair | Red | 3 | 15 | ChairRed3 |
Car | Blue | 1 | 5 | CarBlue1 |
Car | Green | 2 | 20 | CarGreen2 |
Car | Green | 4 | 20 | CarGreen4 |
Prediction
Category | Colour | Day_of_Prediction | day_of_Sales | Prediction | ID |
Chair | Blue | 3 | 3 | 10 | ChairBlue3 |
Chair | Blue | 3 | 2 | 10 | ChairBlue2 |
Chair | Blue | 3 | 1 | 5 | ChairBlue1 |
Chair | Blue | 2 | 2 | 10 | ChairBlue2 |
Chair | Blue | 2 | 1 | 30 | ChairBlue1 |
Chair | Blue | 1 | 1 | 10 | ChairBlue1 |
Chair | Red | 3 | 3 | 20 | ChairRed3 |
Chair | Red | 1 | 1 | 20 | ChairRed1 |
Table | Blue | 3 | 1 | 10 | TableBlue1 |
Table | Blue | 3 | 2 | 5 | TableBlue2 |
Table | Blue | 4 | 2 | 5 | TableBlue2 |
(day of sales represents the number of days before an event the product is sold, and day of prediction represents the number of days before this event that the prediction is done - so day_of_prediction >= day_of_sales)
From these tables, I have create three others : Product_day (have all distinct combination of ID=Category&&Colour&&day of sales), day_of_prediction (have all the distinct day_of_prediction) and day_of_sales (have all the distinct day_of_sales) and organized them this way :
From this I have created this matrix (summing prediction of sales per day of sales and day of prediction :
days of sales / days of prediction | 1 | 2 | 3 | 4 |
1 | 30 | 30 | 15 | |
2 | 10 | 15 | 5 | |
3 | 30 |
What I need is to create a measure that would allow me for every combination of day_of_sales and day_of prediction to have the future sales for this specific day of prediction but for the same and future day of sales.
The final results of this measure in a matrix would be (I have put the calculation between parenthesis) :
days of sales / days of prediction | 1 | 2 | 3 | 4 |
1 | 30 | 30 | 15 | |
2 | 40 (10+30) | 30 (15+15) | 5 | |
3 | 60 (30+15+15) |
Any help or advice would be highly appreciated !
Thanks
Solved! Go to Solution.
Hi, @Anonymous
Thanks for your quick response !
You can try to use this dax code:
Measure = var _t =ADDCOLUMNS( CROSSJOIN( ALLSELECTED('day_of_sales'[day_of_Sales]) , ALLSELECTED('day_of_prediction'[Day_of_Prediction])) , "value" , CALCULATE( SUM('Prediction'[Prediction])))
var _cur_sales=MAX('Prediction'[day_of_Sales])
var _cur_pre = MAX('Prediction'[Day_of_Prediction])
var _t2 = FILTER(_t, [Day_of_Prediction]=_cur_pre && [day_of_Sales]<=_cur_sales)
return
IF(ISBLANK(_cur_pre),BLANK(),SUMX(_t2,[value]))
The result is as follows:
Thank you for your time and sharing, and thank you for your support and understanding of PowerBI!
Best Regards,
Aniya Zhang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly
Hi , @Anonymous
Thanks for your sample data first~
According to your description, you want to get the sum of the final Matrix visual in your image , and it seems the value is all from the 'Prediction' Table.
Here are the steps you can refer to :
(1)This is my test data:
(2)We can create a measure like this:
Measure = var _t =ADDCOLUMNS( CROSSJOIN( ALLSELECTED('day_of_sales'[day_of_Sales]) , ALLSELECTED('day_of_prediction'[Day_of_Prediction])) , "value" , CALCULATE( SUM('Prediction'[Prediction])))
var _cur_sales= MAX('day_of_sales'[day_of_Sales])
var _cur_pre = MAX('Prediction'[Day_of_Prediction])
var _t2 = FILTER(_t, [Day_of_Prediction]=_cur_pre && [day_of_Sales]<=_cur_sales)
return
SUMX(_t2,[value])
Then we can put it on the visual and we can get this result as follows:
Thank you for your time and sharing, and thank you for your support and understanding of PowerBI!
Best Regards,
Aniya Zhang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly
Hi v-yueyunzh-msft ! Thank your for your answer !
It seems to work well excepted in the case when there are days_of_prediction=0.
For instance if we take this data as the prediction table:
Category | Colour | Day_of_Prediction | Sum of day_of_Sales | ID |
Chair | Blue | 0 | 0 | ChairBlue1 |
Chair | Blue | 1 | 0 | ChairBlue1 |
Chair | Blue | 1 | 1 | ChairBlue2 |
Chair | Blue | 2 | 0 | ChairBlue1 |
Chair | Blue | 2 | 1 | ChairBlue2 |
Chair | Blue | 2 | 2 | ChairBlue3 |
Chair | Red | 0 | 0 | ChairRed1 |
Chair | Red | 2 | 2 | ChairRed3 |
Table | Blue | 2 | 0 | TableBlue1 |
Table | Blue | 2 | 1 | TableBlue2 |
Table | Blue | 3 | 1 | TableBlue2 |
The results are :
We can see values from days_of_prediction=0 go to cases when it is not supposed to go.
Do you know how that can be solved ?
Thank you !
Hi, @Anonymous
Thanks for your quick response !
You can try to use this dax code:
Measure = var _t =ADDCOLUMNS( CROSSJOIN( ALLSELECTED('day_of_sales'[day_of_Sales]) , ALLSELECTED('day_of_prediction'[Day_of_Prediction])) , "value" , CALCULATE( SUM('Prediction'[Prediction])))
var _cur_sales=MAX('Prediction'[day_of_Sales])
var _cur_pre = MAX('Prediction'[Day_of_Prediction])
var _t2 = FILTER(_t, [Day_of_Prediction]=_cur_pre && [day_of_Sales]<=_cur_sales)
return
IF(ISBLANK(_cur_pre),BLANK(),SUMX(_t2,[value]))
The result is as follows:
Thank you for your time and sharing, and thank you for your support and understanding of PowerBI!
Best Regards,
Aniya Zhang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly
Thank you !
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 |
---|---|
112 | |
97 | |
82 | |
67 | |
61 |
User | Count |
---|---|
150 | |
120 | |
99 | |
87 | |
68 |