Create measures based on values from two tables

[Anonymous]

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

[v-yueyunzh-msft]

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

[Anonymous]

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 !

[v-yueyunzh-msft · Accepted 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

[Anonymous]

Thank you !