Hello Experts,
the following problem robs me of sleep:
These is the main data:
Houses | Region | Housenames | Finisching date | Year | Phase | General Planned Housecosts | Jan Costs 2024 | Feb costs 2024 | ... |
House1 | W | Mouse | Jan 2024 | 2024 | 1 | 100 | 10 | 60 | |
House2 | N | Cat | Feb 2024 | 2024 | 2 | 50 | 502026 | 90 | |
House3 | W | Dog | Jan 2024 | 2024 | 1 | 300 | 70 | 10 | |
House4 | W | Rabbit | Jan 2024 | 2024 | 5 | 500 | 90 | 5 | |
For this I want to calculate the costs at each month. This means
If the house is at phase 1 I will take the Costs for each month of a column ( Jan consts, feb costs etc.) only for the existing year; if it is at another phase I will divide the general planned costs per 12 ( e. g. House2=4,166 per Month). The duration is always a year/ 12 month.
My problem is, that I don't want to rearrange the data, because otherwise I will get a lot of duplicate information at the table ( Name of the Houses etc.).
I also tried to split the existing table with the dimension data and the costs of the month to get something like, with a connection about the Houses ( House1-House1, etc)
Table 1
Houses | Region | Housenames | Phase | General Planned Housecosts |
House1 | W | Mouse | 1 | 100 |
House2 | W | Cat | 2 | 50 |
House3 | N | Dog | 1 | 300 |
House4 | W | Rabbit | 5 | 500 |
Table 2
Houses | Month | Costs |
House1 | Jan 2024 | 10 |
House1 | Feb 2024 | 60 |
House2 | Jan2024 | 502026 |
House2 | Feb2024 | 90 |
... | ... | ... |
But in this case I don't know how to get further to summarize above the 2 tables automatically by using a calender table, which is connected to the finishing date and the columns...
So for my example here I have to get the results like:
Region | Jan 2024 | Feb 2024 | | |
W | 10 (H1)+70 (H3)+500/12 (H4) | 60+10+5/12 | | |
N | 50/12 | 50/12 | | |
| | | | |
So here I have to add either columns and a sum over the rows or connect different tables…
Without using the Month-Column-Values the dax is okay:
Region=
var YEAR_min = min(Calendar_ProjectFinishDate[Year])
var YEAR_max = max(Calendar_ProjectFinishDate[Year])
return
calculate(sumx('Houses','Houses'[General Planned Housescosts]/12),DATESINPERIOD(Calendar_ProjectFinishDate[Date], max(Calendar_ProjectFinishDate[Date]), -1,year) ,'Houses'[Year]>=YEAR_min && 'Houses'[Year]<= YEAR_max
)
I also tried something with switch or if, but I failed...
I hope you could follow my explanation. Many thanks for any tips!
