Categorization of values at specific dates

[Till__]

Dear all, 

I have the following table with name "Härtegrad" with two measures which have a specifc value at a time and the value only changes if the category changes for a measures. My goal is to achieve a graph over time, where I can see at each date how much Savings are in a specifc category.  

Above you can see that at the 10. September 2022 6 are in category IL 1 and on the 10. Oktober 2022 56 of total savings are in category 1. The difficulty for me occurs on the 11. Oktober when I have total savings of 57 but 7 are in Category IL 2 and 50 still remain in category IL1. At the last date the 24. Dezember 2024 a total value of 70 is again then in the last category IL5. 

DateCategory =

GENERATE(

    DISTINCT('Härtegrad'[Date of Change]),

    DISTINCT('Härtegrad'[Category])

)

I was trying to work with a new table "DateCategory" which makes a distinction of every date and assings each category to it. 
However, the problem for me is now that I am not able to caluclate the correct value in each category at each date of change. 

Using the example from above I want to achieve that at the 10. September 2022 all entries in the values columns are zero except in Category IL1 a value of 6 is existing. At the 10. Oktober 2022  all entries in the values columns are zero except in Category IL1 with a value of 56. At the 11. Oktober 2022 only three category matches are zero but in category IL1 a value of 50 and in category IL2 a value of 7 (which was 6 before - that is the saving in a category can change) is observed. This goes then until the end when at the 24. Dezember 2024  all corresponding entries in the values columns are zero except the one matching he Category IL 5 with a value of 70. 

I was trying to code the value column with this code: 

Value =

CALCULATE(

    SUM('Härtegrad'[Savings]),

    FILTER(

        ALL('Härtegrad'),

        'Härtegrad'[Date of Change] <= MAX('DateCategory'[Date of Change]) &&

        'Härtegrad'[Category] = MAX('DateCategory'[Category])

    )

)


However this assigns to each date and category a value of 56. I am extremely frustrated and do not know any real or other solution that would work. 

Thank you all in advance!!

Best Till 

[amitchandak]

@Till__ , I think need to have an independent date table  and a category table. Date you can keep independent without join and category you can join and use in slcie r

Caregory = DISTINCT('Härtegrad'[Category])

Calendar = Addcolumns(calendar(date(2020,01,01), date(2021,12,31) ), "Month no" , month([date])
, "Year", year([date])
, "Month Year", format([date],"mmm-yyyy")
, "Month year sort", year([date])*100 + month([date])
, "Qtr Year", format([date],"yyyy-\QQ")
, "Qtr", quarter([date])
, "Month",FORMAT([Date],"mmmm")
, "Month sort", month([DAte])
, "Is Today" ,if([Date]=TODAY(),"Today",[Date]&"")
,"Day of Year" , datediff(date(year([DAte]),1,1), [Date], day)+1
, "Month Type", Switch( True(),
eomonth([Date],0) = eomonth(Today(),-1),"Last Month" ,
eomonth([Date],0)= eomonth(Today(),0),"This Month" ,
Format([Date],"MMM-YYYY") )
,"Year Type" , Switch( True(),
year([Date])= year(Today()),"This Year" ,
year([Date])= year(Today())-1,"Last Year" ,
Format([Date],"YYYY")
)
)

Value =
CALCULATE(
SUM('Härtegrad'[Savings]),
FILTER(
ALL('Date'),
'Date'[Date] <= MAX('Date'[Date])
)
)

[Till__]

If I use your value calculation this does not work for me. I do have an independent Calendar table but the matching with the Category and Date of Change still does not work...