Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
I have 2 tables, 1 has monthly data and one has daily data.
I want to multiply my daily value by my monthly value in a measure so I can later splice the sum of cost per month
Sample Data:
Monthly Data | |
Date | Rate |
1/1/2018 | 2 |
2/1/2018 | 3 |
3/1/2018 | 4 |
Daily Data | (Desired output) | |
Date | Amount | Cost |
1/1/2018 | 10 | 20 |
1/2/2018 | 11 | 22 |
1/3/2018 | 12 | 24 |
1/4/2018 | 11 | 22 |
2/1/2018 | 10 | 30 |
2/2/2018 | 11 | 33 |
2/3/2018 | 11 | 33 |
2/4/2018 | 10 | 30 |
3/1/2018 | 10 | 40 |
3/2/2018 | 10 | 40 |
3/3/2018 | 11 | 44 |
3/4/2018 | 12 | 48 |
Solved! Go to Solution.
Hi,
You may refer to my solution in this PBI file.
Hope this helps.
Try this:
[Cost] = SUMX ( DailyTable, DailyTable[Amount] * LOOKUPVALUE ( MonthlyTable[Rate], MonthlyTable[Date], DailyTable[Date] ) )
Thanks for your reply!
Unfortunately this measure returns [first day of the month] * [monthly rate ] ; not a sum of the cost each day per month
@Anonymous
Try this MEASURE
CostMeasure = CALCULATE ( SUM ( MonthlyTable[Rate] ), FILTER ( MonthlyTable, MONTH ( SELECTEDVALUE ( DailyTable[Date] ) ) = MONTH ( MonthlyTable[Date] ) ) ) * SUM ( DailyTable[Amount] )
@Anonymous
See the attached pbix file
Thank you for the reply @Zubair_Muhammad,
this works, but it does not work for when the tables are connected to a common date table unfortunately. Sorry for the misunderstanding.
See pbix file not sure how to upload pbix file,
I have a custom table
Calendar =
CALENDAR ("2012-1-1" , "2021-01-01")
with both tables linked to it. Any ideas?
Hi,
You may refer to my solution in this PBI file.
Hope this helps.
User | Count |
---|---|
93 | |
83 | |
77 | |
75 | |
66 |
User | Count |
---|---|
115 | |
105 | |
93 | |
65 | |
60 |