Rolling cumulative totals

[KateEC]

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! 🙂 

[v-xjiin-msft]

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.

[Floriankx]

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]

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]

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]

Thank you 🙂

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

[KateEC]

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