Budget Sum per Year for Each Row of the Same Year

[TimmK]

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

)

[tamerj1]

Hi @TimmK 
Almost there

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

[TimmK]

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.

[v-kkf-msft · Accepted solution]

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.