Sum of Count if

[maustin02]

Hi All,

I am still new to PowerBI and seeing if anyone could help me out. I know it can be down but I am struggeling to get a measure set up that has a rolling sum based on the date. I have gotten this work in excel but struggeling to repodcue it in PowerBI. I have a data set that contains dates, "modfied date" and then "comment date". The first measure i am trying to create is "Closed", in this measure i am trying to identify unique dates and in the "modified date". Then total up the count of thoes dates that are less than or equal to that date. The second measure is "Open" this meause would give a rolling sum as well, but it would only sum if the comment date is less than or equal to the date modified. 

Below are two screenshots:

- First one is me producing the correct results in excel 

- Second image is me taking a stab at reporducing it in PowerBI

[AntrikshSharma · Accepted solution]

@maustin02  Let me know if this works for you, refer to the file attached below my signature:

[maustin02]

Thank you so much for the help this is prefect. I have one question about the set up. Is there a reason why you generated a date table for the graph instead of using the date modifed field as the x axis? 

[AntrikshSharma]

@maustin02 Yes, it is actually a best practice to have a date table in your PBI model, otherwise PBI will generate a separate hidden date table for each column of your model, refer to the below images.

If you don't have a date table, you will have to keep the auto date time option ON.

If you keep it checked then these type of hidden date tables are created in the model. They increase the size of the model and you can't reuse them, meaning date table created from one column can't filter other columns/table and that is generally a very common business scenario.

Also I have used DAX to create a date table as I have to solve questions and DAX gives me a fast way of creating a mock up file, I would suggest you create a date table in Power Query.

[Fowmy]

@maustin02 


Can you share the sample data with the expected results for one or two in an excel or csv format?

Can you share some sample data and the expected result to have a clear understanding of your question?
You can save your files in OneDrive, Google Drive, or any other cloud sharing platforms and share the link here.
____________________________________
How to paste sample data with your question?
How to get your questions answered quickly?

_____________________________________
Did I answer your question? Mark this post as a solution, this will help others!.

Click on the Thumbs-Up icon if you like this reply 🙂

YouTube, LinkedIn

[maustin02]

Below is a link to an excel file that contains sample data and also my end goal within PowerBI

https://1drv.ms/x/s!AtrxZbQBYb0Ljat6ijak9uwu74n3Iw?e=J5KALD