DAX to show same value for missing months

[niyaz006]

My data

Category	Sub category	Date	Value
Cat1	Subcat1	1-Jan	1
Cat1	Subcat2	1-Jan	1
Cat2	Subcat3	1-Jan	1
Cat2	Subcat4	1-Jan	1
Cat1	Subcat1	1-Feb	2
Cat1	Subcat2	1-Mar	2
Cat2	Subcat4	1-Mar	3

The value ranges from 1 to 5. I am looking to create a table like below

Month	#Value 1s	#Value 2s	#Value 3s	# Value 4s	# Value 5s
1-Jan	4	0	0	0	0
1-Feb	3	1	0	0	0
1-Mar	1	2 (1+1)	1	0	0

In Feb, 1 entry's has value changed to 2. Hence, that row from the previous month is ignored, but every other record (3 x 1s) is counted

I tried something like the below:

Cumulative Value Count = 
CALCULATE(
    COUNTROWS(SUMMARIZE(Table, [Cat], [Subcat])),
    FILTER(ALL(Table[date]), 
        Table[startdate] <= MAX(Table[date])
        )
    )

 But my output was (it is not reducing the value 1s when they have moved to 2s and 3s, ....

Month	#Value 1s	#Value 2s	#Value 3s	# Value 4s	# Value 5s
1-Jan	4	0	0	0	0
1-Feb	4	1	0	0	0
1-Mar	4	2	1	0	0

Any help/guidance?

[VahidDM]

Hi @niyaz006 

can you please add more details about the output? what are those values1 to values 5 in the table? 

Appreciate your Kudos!! 

LinkedIn | Twitter | Blog | YouTube 

[niyaz006]

The value column can have values from the range (1 to 5). These are like levels. Level 1 to 5. The Level might increase from 1 to 2 in a month or 2 or even more. Lets say a metric Category 1 and Subcategory1 is Level 1 in Jan. We will get this data. The next time we get the data is when it changes to Level 2. Lets say it happens in March. So I need to show Level 1 in Jan and Feb. And Level 2 from March.

In the output table, I am summarising how many metrics are in level 1 to level 5 in each month.