Replacing blanks with previous count in continuous line chart

[onedayover]

Hi All

I'm hoping someone will be able to help me modify this measure. I've tried a number of ways to make it work but can't quite seem to get it right. Where there is a blank week (Wk 9 in this example, I would like the figure from the previous week, e.g. 100 (Wk 8), so that my line is continuous and doesn't drop to 0. My measure so far is:

2024_cate_tickets =
COUNTROWS (
FILTER (
ALL ( 'cate' ),
'cate'[week_num] <= MAX ( 'cate'[week_num] )
&& 'cate'[date_registered] <= MAX ( 'cate'[date_registered] )
&& 'cate'[event] = "cate 2024"
)
)

[onedayover]

Hi aduguid, apologies for the delay in getting back to you. You were correct on both counts - I wasn't using a dimension table and the cate table did not contain wk9 data. I fixed this as you outlined and got the graph to display exactly how I needed it to. Thanks again for your help 🙂

[Jihwan_Kim]

Hi,

I am not sure how your semantic model looks like, but I assume you are not using dimension table, or I assume cate table does not contain wk9 data.

Please try using date dimension table.

1. Create date dimension table.

2. Create a relationship between date dimension table & cate table

3. Use x-axis from date dimension table

4. In the measure, input column names from date dimension table.

[aduguid · Accepted solution]

2024_cate_tickets = 
VAR CurrentCount = COUNTROWS (
    FILTER (
        ALL ( 'cate' ),
        'cate'[week_num] <= MAX ( 'cate'[week_num] )
        && 'cate'[date_registered] <= MAX ( 'cate'[date_registered] )
        && 'cate'[event] = "cate 2024"
    )
)
RETURN
    IF(
        ISBLANK(CurrentCount),
        CALCULATE(
            LASTNONBLANK('cate'[week_num], CurrentCount),
            FILTER(ALL('cate'), 'cate'[week_num] < MAX('cate'[week_num]))
        ),
        CurrentCount
    )

[onedayover]

Hi aduguid,
Thank you for your reply.
I just tried your code and it the graph didn't change. Do you have any further suggestions?
Thanks