cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Frequent Visitor

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 

derickyy_0-1606849208386.png

 

Here is my code:

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

 

Thanks! 

 

2 REPLIES 2
Community Support
Community Support

@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

Super User III
Super User III

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.



Did I answer your question? Mark my post as a solution!
Did my answers help arrive at a solution? Give it a kudos by clicking the Thumbs Up!

DAX is for Analysis. Power Query is for Data Modeling


Proud to be a Super User!

MCSA: BI Reporting

Helpful resources

Announcements
secondImage

Congratulations!

We are excited to announce the Power BI Super Users!

Wave Release 2

Check out the updates in Power BI.

Overview of Power BI 2020 release wave 2!

Microsoft Ignite

Microsoft Ignite

Join digitally, March 2–4, 2021 to explore new tech that's ready to implement. Experience the keynote in mixed reality through AltspaceVR!

secondImage

The largest Power BI virtual conference

100+ sessions, 100+ speakers, Product managers, MVPs, and experts. All about Power BI. Attend online or watch the recordings.

Top Solution Authors