topic Re: Calulating help in DAX Commands and Tips

Calulating help

Anonymous — Mon, 04 Sep 2023 11:32:36 GMT

Hello !

How do you calculate minus in power bi, i.e. I have a baseline = 208 and then I have another field with values to be accumulated downwards, see my example:

Forecast is calculated from another variable called "Effekt CO2 (sustainability)"

ÅR	Prognos

2022	208
2023	208
2024	208
2025	187,18
2026	187,18
2027	182,58
2028	182,58
2029	176,28
2030	176,28

Re: Calulating help

some_bih — Mon, 04 Sep 2023 19:54:36 GMT

Hi Anonymous "it depends 🙂", provide more model data, relationships and for variable to get some feedback as possible solution.

Re: Calulating help

Anonymous — Tue, 05 Sep 2023 07:30:25 GMT

Hello again! That is, I want the calculations to start at 208, but then it should be accumulated based on this table:

Re: Calulating help

some_bih — Tue, 05 Sep 2023 07:56:52 GMT

Hi Anonymous Inforande is Date type column? In column Basline there will be always single amount for each year, like 208?

Re: Calulating help

Anonymous — Tue, 05 Sep 2023 08:00:39 GMT

Hi Anonymous Informande is Date type column? It is only a date column with Year

In column Baseline there will always be a single amount for each year, like 208? Yes only 208, but I don't know if I'm thinking wrong or if the year 2022 should start with 208 and then calculate it based on "Summa of Effect CO2 (sustainability)"

Re: Calulating help

some_bih — Tue, 05 Sep 2023 11:37:54 GMT

Hi Anonymous for solution as below, please create Date / Calendar Table in Power BI and connect / create relationship with your Year column.

After that please create following measures 83), adopt Sheet1 with your table name

To get visuals as below, please use Year colum from created Date / Calendar table

Sum of Base = SUM(Sheet1[Baseline])

Sum of Effekt CO2 = sum(Sheet1[Effekt CO2])

Running total =

VAR __max_date = MAX ( 'Date'[Date] )

VAR __base_line=[Sum of Base]

VAR __running_total=

CALCULATE (

[Sum of Effekt CO2],

'Date'[Date] <= __max_date,

ALL ( 'Date' )

)

VAR __Result=__running_total+__base_line

RETURN __Result

Did I answer your question? Kudos appreciated / accept solution!

Re: Calulating help

Anonymous — Wed, 06 Sep 2023 09:15:50 GMT

Hello !

The first solution worked great, but now when I want to filter on "åtgärder" or "Skede" it calculates wrong, do you know how to fix this? For exampel se he picture

I can add the test file I have if it easyier ?

Re: Calulating help

some_bih — Wed, 06 Sep 2023 09:58:01 GMT

Hi Anonymous measure for this case should use date related columns from Date / Calendar table

To get visuals as below, please use Year colum from created Date / Calendar table