Forum Discussion

derickyy's avatar
derickyy
Frequent Visitor
5 years ago

Cumulative Count and Empty Column

Hey everyone,

 

I was trying to build a rolling cumulative count column chart on PowerBI. And the count is weekly basis (each column represents the total number of rows in the table before that week). Because all the columns will be used as Filters,  I used 'ALLSELECTED' function.

 

Here is the problem: if I started to apply the filter and the cumulative count didn't change between two or more weeks, the column chart would show an empty column for those weeks. How can I fill in those empty columns with the same columns in the previous week? I'm not sure this is due to the default setting of PowerBI or my code 

 

Here is my code:

CumCount = CALCULATE(COUNTA('Table'[ID]),FILTER(ALLSELECTED('Table'),'Table'[StartofWeek] <= MAX('Table'[StartofWeek])))

 

Thanks! 

 

Need Help
Show and Tell
Tips and Tricks

2 Replies

  • edhans's avatar
    edhans
    Community Champion
    5 years ago

    If there is no data to include, you cannot in DAX. You need to add through Power Query or the source system values of 0 for the missing week.

     

    If the weeks exist in your fact table but are blank or null in Power Query, replace with 0 before loading.

     

    If you have a date table from Power Query, you could create an anti-join on your data table and date table, keeping only dates/weeks that don't exist in your data, then append it to the bottom of your table with $0 value.

  • Anonymous's avatar
    Anonymous
    Not applicable
    5 years ago

    derickyy 

     

    It looks doable but I am not sure about what leads to empty bar. Can you share the a sample of your current data?

     

    Paul