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.
Hi, I'm new to Power BI. Trying to create a report based on the data available from 2020- 2023. Please find the beow sample data.
Item code | Dispatch date | Estimated Cost | Arrival Date | Actual Cost |
I-001 | 7/1/2022 | 100 | 8/1/2022 | 200 |
I-002 | 7/4/2022 | 200 | 8/4/2022 | 300 |
I-003 | 8/23/2022 | 300 | 9/3/2022 | 400 |
I-004 | 8/24/2022 | 400 | 9/20/2022 | 500 |
I-005 | 9/1/2022 | 500 | 9/9/2022 | 600 |
I-006 | 9/2/2022 | 600 | 10/2/2022 | 700 |
From the above table , can you help me to generate chart on monthly basis. 1.Need to Display Total Actual cost / Monthly and 2. Total Estimated cost / Monthly . End User will select Year and Month as a filter option. Both 1 and 2 should be in same chart.
Thank you for your help
Solved! Go to Solution.
Hi, @krishnavzm
You can try the following methods. Divide your sample data into 2 tables.
Table1:
Table2:
New Table:
Date = CALENDAR(MIN('Table 1'[Dispatch Date]),MAX('Table 2'[Arrival Date]))
Column:
Year = YEAR([Date])
Month = MONTH([Date])
Measure:
Total Actual cost / Monthly =
CALCULATE (
SUM ( 'Table 2'[Actual Cost] ),
FILTER (
ALL ( 'Date' ),
[Year] = SELECTEDVALUE ( 'Date'[Year] )
&& [Month] = SELECTEDVALUE ( 'Date'[Month] )
)
)
Total Estimated cost / Monthly =
CALCULATE (
SUM ( 'Table 1'[Estimated Cost] ),
FILTER (
ALL ( 'Date' ),
[Year] = SELECTEDVALUE ( 'Date'[Year] )
&& [Month] = SELECTEDVALUE ( 'Date'[Month] )
)
)
Result:
Is this the result you expect?
Best Regards,
Community Support Team _Charlotte
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi, @krishnavzm
You can try the following methods. Divide your sample data into 2 tables.
Table1:
Table2:
New Table:
Date = CALENDAR(MIN('Table 1'[Dispatch Date]),MAX('Table 2'[Arrival Date]))
Column:
Year = YEAR([Date])
Month = MONTH([Date])
Measure:
Total Actual cost / Monthly =
CALCULATE (
SUM ( 'Table 2'[Actual Cost] ),
FILTER (
ALL ( 'Date' ),
[Year] = SELECTEDVALUE ( 'Date'[Year] )
&& [Month] = SELECTEDVALUE ( 'Date'[Month] )
)
)
Total Estimated cost / Monthly =
CALCULATE (
SUM ( 'Table 1'[Estimated Cost] ),
FILTER (
ALL ( 'Date' ),
[Year] = SELECTEDVALUE ( 'Date'[Year] )
&& [Month] = SELECTEDVALUE ( 'Date'[Month] )
)
)
Result:
Is this the result you expect?
Best Regards,
Community Support Team _Charlotte
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Thanks for the help. I got an idea now. Will work on the actual model
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 |
---|---|
110 | |
94 | |
80 | |
67 | |
59 |
User | Count |
---|---|
150 | |
119 | |
104 | |
87 | |
67 |