cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Frequent Visitor

## Calculating sum of two measures, with different dependencies on slicer

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?

1 ACCEPTED SOLUTION
Frequent Visitor

Hi,

I managed to figure out a solution to my problem, which turned out to be rather trivial.

M_Remaining_BudgetAmount_Year =
VAR __MaxMonth = MAX('Time'[Date] )

RETURN
CALCULATE(
[M_BudgetAmount], FILTER(ALL('Time'), 'Time'[Year] = YEAR(__MaxMonth) && 'Time'[Month] > MONTH(__MaxMonth))
)
Which is practically what @HikingBIKing replied earlier, just complementing it with a constraint on the year in question.
Thanks a lot for all help!

8 REPLIES 8
Community Support

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

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.

Frequent Visitor

Hi,

I managed to figure out a solution to my problem, which turned out to be rather trivial.

M_Remaining_BudgetAmount_Year =
VAR __MaxMonth = MAX('Time'[Date] )

RETURN
CALCULATE(
[M_BudgetAmount], FILTER(ALL('Time'), 'Time'[Year] = YEAR(__MaxMonth) && 'Time'[Month] > MONTH(__MaxMonth))
)
Which is practically what @HikingBIKing replied earlier, just complementing it with a constraint on the year in question.
Thanks a lot for all help!

Regular Visitor

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:

1. You have a calendar dimension table with a MonthNumber column, e.g. 1 for Jan, 2 for Feb etc.
2. Your budget data is in a fact table. You'll need to rename this and the budget column accordingly in the DAX measure.
Frequent Visitor

Here are the tables that are relevant:

'Time'[Month Name Current] is currently used to hold the slicer values.

Regular Visitor

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 )
)``````

Frequent Visitor

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.

Regular Visitor

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.

Frequent Visitor

Completely different fields, set by other slicers in the report. Different departments, accounting groups, etc.

Announcements

#### Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.