Replce blank with last value in dax

[F_mmb]

I have a table that includes order details, with columns for order year and category as shown in the sample below:

Id	category	Year
1	x	2020
2	x	2020
3	y	2020
4	y	2020
5	y	2020
8	z	2020

I want to count the cumulative number of orders I have per year and category. So, I created the following measures.

IDCount = COUNT(Fact[Id])

CumulativeIDCount =
CALCULATE(
SUMX(
FILTER(
ALL(Fact),
Fact[Year] <= MAX(Fact[Year]) &&
Fact[category] = SELECTEDVALUE(Fact[category])
),
[IDCount]
)
)

Then, I used a matrix to present the results, and it is quite what I want. However, for empty values, I want to replace the value with the last value before it.

the result:

cetegory	2014	2015	2016	2017	2018	2019	2020	2021	2022	2023	2024
x									1
y	34	84	90	46	70	42	84	79	57	4473	4516
z	6			7
f			13						14
t	18	19			20	24		25	32	33

the result I expected: 

cetegory	2014	2015	2016	2017	2018	2019	2020	2021	2022	2023	2024
x									1	1	1
y	34	84	90	46	70	42	84	79	57	4473	4516
z	6	6	6	7	7	7	7	7	7	7	7
f			13	13	13	13	13	13	14	14	14
t	18	19	19	19	20	24	24	25	32	33	33

I need your help to modify the current DAX measure to handle blank values as fill down approach.

[Anonymous · Accepted solution]

Thanks for the reply from @lbendlin , please allow me to provide another insight: 

Hi  @F_mmb ,

Here are the steps you can follow：

1. Create measure.

Test =
var _year=
MAXX(
    FILTER(ALL('Table'),
    'Table'[Category]=MAX('Table'[Category])&&
    [CumulativeIDCount]<>BLANK()&&'Table'[Year]<MAX('Table'[Year])),[Year])
return
IF(
    [CumulativeIDCount]=BLANK(),
SUMX(
    FILTER(ALL('Table'),
    'Table'[Category]=MAX('Table'[Category])
    &&
   'Table'[Year]=_year&&[CumulativeIDCount]<>BLANK()
    ),[CumulativeIDCount]),
    [CumulativeIDCount])

2. Result:

Best Regards,

Liu Yang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly

[F_mmb]

Thank you. The issue is that IDCount is not a column; it is a measure, as I mentioned in the post.

[lbendlin]

However, for empty values, I want to replace the value with the last value before it

To report on things that are not there you need to use disconnected tables and/or crossjoins