The ultimate Microsoft Fabric, Power BI, Azure AI, and SQL learning event: Join us in Stockholm, September 24-27, 2024.
Save €200 with code MSCUST on top of early bird pricing!
Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started
Hi community,
I have the following issue.
I am trying to update a report that is used to report on actual costs, budget cost and prognosis cost (based on selected months in a slicer).
In other words, for 2024, let's say I have selected the following months in my slicer: Jan, Feb, Mar.
Then actual cost would report the cost accumulated in Jan, Feb and Mar.
Budget cost would report on the budgeted cost for Jan, Feb and Mar.
But the prognosis for the full year should calculate the following:
Remaining budget cost for the year: Apr - Dec
Prognosis cost for the year = Actual cost (Jan-Mar) + Remaining budget cost (Apr - Dec)
Both of these measures are placed in a matrix visual in the report. The visual containing these measures is disconnected from the slicer that selects dates. When the visual is not disconnected from the slicer, the dates are limited to the selected months in the slicer (Jan, Feb, Mar).
How can I achieve what I want? Both measures used to calculate the prognosis are dependant on the "month slicer", but one should use the selected months, and the other should use the non-selected ones. From my understanding, the visual itself has to be independent of the slicer, so is it possible to have the measure calculations dependent on a slicer in the report?
Regards,
Fredrik
Any ideas?
Solved! Go to Solution.
Hi,
I managed to figure out a solution to my problem, which turned out to be rather trivial.
Hi @oceanfree
Could you please share some data(exclude sensitive data) or create some sample data, so that we can help you better.
It's better to provide some Text format and your expected result with backend logic and special examples.
How to provide sample data in the Power BI Forum
How to Get Your Question Answered Quickly
Best Regards
Zhengdong Xu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi,
I managed to figure out a solution to my problem, which turned out to be rather trivial.
Hi @oceanfree
You could try something like this:
Remaining Budget Cost =
VAR __MaxMonth = MAX( 'Calendar'[MonthNumber] ) -- The maximum month selected from your slicer
RETURN
CALCULATE( SUM( 'Fact'[Budget] )
, FILTER( ALL( 'Calendar'[MonthNumber] ), 'Calendar'[MonthNumber] > __MaxMonth )
)
I've made a few assumptions here:
Here are the tables that are relevant:
'Time'[Month Name Current] is currently used to hold the slicer values.
Here's the DAX code amended according to your model:
Remaining Budget Cost =
VAR __MaxMonth = MAX( 'Time'[Month] ) -- The month number related to the latest month selected from your slicer
RETURN
CALCULATE( [M_BudgetAmount]
, FILTER( ALL( 'Time'[Month] ), 'Time'[Month] > __MaxMonth )
)
Hi,
Does not seem to be working (assuming I should also change 'Calendar' in the latter part of your dax to 'Time'. No values appearing for the measure when added to the visual.
Yes that's correct. I did edit my code to change 'Calendar' to 'Time'.
What values are you adding to the row section of your matrix visual? Is it months or a completely different field.
Completely different fields, set by other slicers in the report. Different departments, accounting groups, etc.
Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.
Check out the August 2024 Power BI update to learn about new features.
User | Count |
---|---|
21 | |
19 | |
18 | |
17 | |
12 |
User | Count |
---|---|
36 | |
34 | |
20 | |
19 | |
15 |