Forum Discussion
DAX to show same value for missing months
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?
2 Replies
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.
