topic Re: Calculating sum of two measures, with different dependencies on slicer in DAX Commands and Tips

Calculating sum of two measures, with different dependencies on slicer

oceanfree — Tue, 07 May 2024 08:09:41 GMT

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?

Re: Calculating sum of two measures, with different dependencies on slicer

HikingBIKing — Tue, 07 May 2024 08:18:41 GMT

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:

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

Re: Calculating sum of two measures, with different dependencies on slicer

oceanfree — Tue, 07 May 2024 08:27:33 GMT

Here are the tables that are relevant:

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

Re: Calculating sum of two measures, with different dependencies on slicer

HikingBIKing — Tue, 07 May 2024 08:33:18 GMT

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

Re: Calculating sum of two measures, with different dependencies on slicer

oceanfree — Tue, 07 May 2024 08:44:13 GMT

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.

Re: Calculating sum of two measures, with different dependencies on slicer

HikingBIKing — Tue, 07 May 2024 08:45:56 GMT

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.

Re: Calculating sum of two measures, with different dependencies on slicer

oceanfree — Tue, 07 May 2024 08:49:31 GMT

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

Re: Calculating sum of two measures, with different dependencies on slicer

Anonymous — Fri, 10 May 2024 05:27:46 GMT

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.

Re: Calculating sum of two measures, with different dependencies on slicer

oceanfree — Fri, 10 May 2024 07:10:57 GMT

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!