topic Re: Budget Sum per Year for Each Row of the Same Year in DAX Commands and Tips

Budget Sum per Year for Each Row of the Same Year

TimmK — Fri, 06 May 2022 10:45:04 GMT

I have the three tables budget, revenue and date that I use for a matrix table in my report. The date column of the date table is connected to the date colums of the budget and revenue table. I use the date format DD.MM.YYYY.

As you can see below I want to see the budget sum of the whole year for each year for each row. Thus, for 2022 I want that each row is 17500 and for 2021 and I want that each row is 13100. However, when I apply my DAX formula I get 30600 instead for each row (which is the sum of 17500 and 13100).

What DAX formula should I use to get the desired result?

This is the DAX formula I currently use:

CALCULATE(

SUM(

Budget[Budget]),

ALLEXCEPT('Date','Date'[Month])

)

Re: Budget Sum per Year for Each Row of the Same Year

tamerj1 — Fri, 06 May 2022 12:44:42 GMT

Hi TimmK
Almost there

Budget I Want = CALCULATE( SUM( Budget[Budget]), ALLEXCEPT('Date','Date'[Year]) )

Re: Budget Sum per Year for Each Row of the Same Year

TimmK — Mon, 09 May 2022 07:04:32 GMT

Thanks, but this formula still results in "Budget I get". What could be possibly the reason? In the data model I connected the date fields correctly. Also, the column "Budget" is displayed correctly, it shows the correct values for each month and year.

Re: Budget Sum per Year for Each Row of the Same Year

v-kkf-msft — Tue, 10 May 2022 06:58:53 GMT

Hi TimmK ,

I think you should be using the date hierarchy in the matrix, then try the following formula:

Measure = CALCULATE( SUM( Budget[Budget]), ALLEXCEPT('Date','Date'[Date].[Year]) )

If the problem is still not resolved, please provide detailed error information or the expected result you expect. Let me know immediately, looking forward to your reply.
Best Regards,
Winniz
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.