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.
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.
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 |
---|---|
114 | |
99 | |
75 | |
73 | |
49 |
User | Count |
---|---|
145 | |
109 | |
109 | |
90 | |
64 |