Forum Discussion
PatrickWong
Helper I
Helper IMultiply Daily Value by Monthly Value and type
I have 2 tables, 1 has monthly data and type and the other has the daily data
I want to multiply my daily value by my monthly value of the correct type in a measure so that I can later splice the sum of the cost by type per month
Sample Data:
| Monthly data | ||
| Date | type | rate |
| 1 January, 2019 | red | 10 |
| 1 January, 2019 | blue | 20 |
| 1 February, 2019 | red | 11 |
| 1 February, 2019 | blue | 21 |
| 1 March, 2019 | red | 12 |
| 1 March, 2019 | blue | 23 |
| Daily Data | (Desired output) | ||
| Date | Amount | Cost | |
| 1 January, 2019 | 10 | red | 100 |
| 2 January, 2019 | 11 | blue | 220 |
| 3 January, 2019 | 12 | blue | 240 |
| 4 January, 2019 | 11 | red | 110 |
| 2 January, 2019 | 10 | red | 110 |
| 2 February, 2019 | 11 | blue | 231 |
| 2 February, 2019 | 11 | red | 121 |
| 2 April, 2019 | 10 | blue | 210 |
| 3 January, 2019 | 10 | blue | 230 |
| 3 January, 2019 | 10 | red | 120 |
| 3 April, 2019 | 11 | red | 132 |
| 3 April, 2019 | 12 | blue | 276 |
PatrickWong add following calculated column and that will do it, change column and table name as per your date model.
Rate = Monthly Data Table
Rate Date = Daily Data Table
Cost =
LOOKUPVALUE( Rate[rate], Rate[Date],EOMONTH('Rate Date'[Date],-1)+1,Rate[type], 'Rate Date'[Type]) *
'Rate Date'[Amount]
1 Reply
- parry2k
Super User
PatrickWong add following calculated column and that will do it, change column and table name as per your date model.
Rate = Monthly Data Table
Rate Date = Daily Data Table
Cost =
LOOKUPVALUE( Rate[rate], Rate[Date],EOMONTH('Rate Date'[Date],-1)+1,Rate[type], 'Rate Date'[Type]) *
'Rate Date'[Amount]