PowerBI Equivalent of Countifs when referencing same-table cell

[Anonymous]

Hey there,

I'm trying to create a calculated column on a date table based on the values of another table. The secondary table has dates, along with a status "Open" or "Closed". I want to graph this to show a running count of how many are open/closed by day.

In Excel, I accomplish this by creating a table with dates on the left side, and an equation that checks to see if the left date is <= the date in the main data table and is the status open, if so, count it.

In PowerBI I've created my date table, and I've been researching for a few hours and have come across using the calculate formula to replicate countifs along with a measure, but I can't seem to find out how to get the calculated formula to reference the left date to check if the date in my secondary table is <= the left-most date.

Any ideas would be greatly appreciated. Thanks!

[Ashish_Mathur]

Hi,

Share some data and show the expected result.

[stretcharm]

Take a look at this video from the @AlbertoFerrari of SQLBI whilst discussing optimisation of dax he shows an example of an Open Orders calculation which sounds similar to your problem.

The whole video is worth watching but the open orders bit starts around 49mins.

https://www.sqlbi.com/tv/dax-optimization-examples/

There are a number of posts on the forum about countif which you can do using CALCULATE count and filters.

[Anonymous]

Hey there,

I'm trying to create a calculated column on a date table based on the values of another table. The secondary table has dates, along with a status "Open" or "Closed". I want to graph this to show a running count of how many are open/closed by day.

In Excel, I accomplish this by creating a table with dates on the left side, and an equation that checks to see if the left date is <= the date in the main data table and is the status open, if so, count it.

In PowerBI I've created my date table, and I've been researching for a few hours and have come across using the calculate formula to replicate countifs along with a measure, but I can't seem to find out how to get the calculated formula to reference the left date to check if the date in my secondary table is <= the left-most date.

Any ideas would be greatly appreciated. Thanks!