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 there,
I am working on a task where I need to compare the revenue for a selected month in one year (2023) to the YTD target (which is <= to the month being selected/filtered on the visual)
To give a concrete example if I filter on March 2023 I want to see the YTD target for Jan up till and incl. March, while if I filter on June I want to compare the YTD target for Jan up till and incl. June.
The idea to visualize it I had to use a gauge visual in PowerBI where if I filter on a selected month I can see what is the revenue (either actual or %) vs. the YTD target.
I tried all kinds fo approaches with TOTALYTD or just via CALCULATE to calculate the YTD Target and the YTD Revenue and nothing seems to work. Any idea on how to fix this would be highly appreciated!
This is a sample of the data:
(There are multiple unique Opp Id's and there are 2 teams with a different monthly target per team.
The revenue is per Opp Id but needs to be aggregated to a Team level to compare vs. target.)
Opp ID | Team | Month | Year | Revenue | Target |
1 | small business | Jan | 2023 | 200 | 540 |
1 | small business | Feb | 2023 | 340 | 565 |
1 | small business | Mar | 2023 | 346 | 400 |
4 | big business | Jan | 2023 | 765 | 700 |
4 | big business | Feb | 2023 | 987 | 1100 |
4 | big business | Mar | 2023 | 3456 | 3000 |
If you want to use time intelligence functions your data model needs to include a calendar table with contiguous and covering dates.
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 |
---|---|
104 | |
95 | |
80 | |
67 | |
62 |
User | Count |
---|---|
147 | |
109 | |
107 | |
85 | |
63 |