Forum Discussion
Calculate total based on corresponding value in another table
Hi, I am trying to bring in total from one table based on the indirect values from another table,
Table1
| Dates1 | Dates2 |
| 25/04/2021 | 26/04/2020 |
Table2 (Data table)
| Dates1 | Total |
| 26/04/2020 | 5 |
| 25/04/2021 | 20 |
| 25/04/2021 | 12 |
| 26/04/2020 | 20 |
In the above example, user selects on date from slicer (Table1 - Dates1 column) but the total should be based on the Table1-Dates2 column. So in above example, if user selects 25/04/2021 then the result should be Total = 25.
I have tried calculate(total, SAMEPERIODLASTYEAR(DATEADD(Table1[Dates2],+1,DAY)) but get blank values.
Hi Rt123
You can create relationship between ‘Table1’[Dates2] and ‘Table2’[Dates1] , filter data from ‘Table1’ to ‘Table2’ , then create a measure to sum the value .
I have attached my pbix file ,you can refer to it .
Best Regards
Community Support Team _ Ailsa Tao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
4 Replies
- amitchandak
Super User
Rt123 ,
Measure =
var _max = maxx(allselected(Table1), Table1[Date1])
var _min= maxx(allselected(Table1), Table1[Date2]) //because date1 is selected from table1
return
calculate(sum(Table2[Total]), filter(Table2, Table2[Date]>= _min && Table2[Date] <=_max))
Thanks amitchandak , but it still gives me total by Date1 instead of Date2.
Hi Rt123
You can create relationship between ‘Table1’[Dates2] and ‘Table2’[Dates1] , filter data from ‘Table1’ to ‘Table2’ , then create a measure to sum the value .
I have attached my pbix file ,you can refer to it .
Best Regards
Community Support Team _ Ailsa Tao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Thank you Anonymous , it worked perfectly. Cant believe it was such simple solution..
