Need help with a measure calculation

[BIlix]

Hello Community,

I have a measure that sums a column in a fact table.

The fact table has a datekey in Format 20240529 and a Datekeyperiod which is 20240501. Both are integers. Datekeyperiod is related to my Datetable. The datekey stores the date on which the entry was submitted to the facttable

I need to calculate my Measure for a Linechart with Year and Month in X Axis and the Measure in Y Axis.

The requirement for e.g. January 2024 is that I calculate all Values in my fact table Where Datekeyperiod is in 2024, eg. >= 20240101 and <= 20241201 AND datekey is <= 20243101 and so on.

The result should be all values for the current business year in context where the submission entry was in or before the current month in context. Therefore the value for February should be the value for january + rows that were submitted on or before february 29th 2024. 

VAR Currentyear = MAX(dim_date[year])
VAR CurrentDatekey = MAX(dim_date[datekey])
VAR MaxDate = MAXX(FILTER(ALL(dim_date), dim_date[year] = Currentyear), dim_date[datekey]) 
VAR MinDate= MINX(FILTER(ALL(dim_date), dim_date[year] = Currentyear), dim_date[datekey]) 

VAR Filteredtable = FILTER(ALL(facttable), (facttable[datekeyperiod] <= MaxDate && facttable[datekeyperiod] >=MinDate) && facttable[datekey] <= CurrentDatekey )  
VAR Result = CALCULATE([Measure], Filteredtable)
RETURN

Result

[sjoerdvn]

Typically, you shouldn't be filtering (or expanding context of) fact columns, but do such things on the dimension tables. Also, be carefull of ALL() as it might also override other filters and slicers. You haven't actually stated what the issue is, but given the above, I would try something like:
 

VAR Currentyear = MAX(dim_date[year])
VAR CurrentDatekey = MAX(dim_date[datekey])

RETURN CALCULATE([Measure]
   , ALL(dim_date)
   , dim_date[year] = Currentyear
   , dim_date[datekey] <= CurrentDatekey 
   , facttable[datekey] <= CurrentDatekey )