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

Rolling cumulative totals

Hi,

 

I am trying to create a chart showing a rolling headcount by month (and also eventually creating it for every week, but not running before I can walk, here!).

 

I absolutely cannot figure out how to do this - In regular excel I had thought I'd use COUNTIF to specify date ranges (probably a messy workaround, but hey!), but COUNTIF doesn't seem to be a valid formual in PowerBI and all the workarounds I've seen mentioned look fairly complex. So, any super-simply worded solutions very welcome! (Although perhaps that doesn't exist for what I'm trying to do...?!) I've got start date and end date and headcount data.

 

Thanks in advance! Smiley Happy 

6 REPLIES 6
KateEC Frequent Visitor
Frequent Visitor

Re: Rolling cumulative totals

I should have mentioned that I am a huge novice when it comes to Power BI/ DAX etc! 

Floriankx Established Member
Established Member

Re: Rolling cumulative totals

Generally there is a possibility how to write countif.

 

CALCULATE(Countrows(Table);Filter....

 

I know this is little helpful if you are not that familiar with PowerBI but it's a start.

 

In your case you should first create a date table with contigous dates you can link with your date column.

KateEC Frequent Visitor
Frequent Visitor

Re: Rolling cumulative totals

Fab - thanks for replying so quickly! I'll have a little research into doing what you suggested!

 

I only have 1 table at the moment, so do i still need the "Table" reference in the formula? 

Floriankx Established Member
Established Member

Re: Rolling cumulative totals

Hello,

 

Countrows requires a table as argument. Only the table name without column.

You may also use Count instead, but I don't use it often.

KateEC Frequent Visitor
Frequent Visitor

Re: Rolling cumulative totals

Thank you Smiley Happy

 

What would be the rest of the formula that you mentioned before? Not used it before Smiley Frustrated 

v-xjiin-msft Super Contributor
Super Contributor

Re: Rolling cumulative totals

Hi @KateEC,

 

Please share us your table stucture and some sample data which we can copy and paste directly and its corresponding expected result if possible. It'll help us a lot to provide a more accurate solution.

 

Thanks,
Xi Jin.