Count The Number Of Days In a Dataset

[MichelleRoberts]

Hello!

I have a report that looks at the total number of parts produced by each of our work cells by date. I have the report filtered to the last calendar month. I need a way to come up with a count for the total number of days. Since we do not run 7 days a week and we do not run on holidays, it's not the total number of days in the month. We also have days where we shut down the plant that are not holidays. So for example, in the screenshot below, I need something that will say there are 26 days in this report's range.

[SivaMani]

@MichelleRoberts , Create a measure 

Count of days = DISTINCTCOUNT(<Date Column>)

[MichelleRoberts]

If I do that, get the following result:

If it's summed:

If I don't sum it:

[SivaMani]

@MichelleRoberts , Can you try DISTINCTCOUNT () in a measure (refer the updated syntax in my previous reply) ?

[MichelleRoberts]

I did and got the same exact results.

[SivaMani]

Can you remove .[Date] from your calculation?

[MichelleRoberts]

I removed the .[Date] and got this result:

[SivaMani]

@MichelleRoberts ,Is there any filter applied in the report and if no filter is applied what should this measure show?

[MichelleRoberts]

The only filter is that the transaction date is in February. I want it to return the number 26 since there were 26 days where we had transactions in the month of February. Does that make sense?

[SivaMani]

Where is the Transaction Date = February applied? Is the filter applied at the visual level? If yes, it won't carry the filter to other visuals. You may need to add it as a slicer and apply the filter.

[MichelleRoberts]

It's applied at the page level but I'm not concerned about that right now. I just need to find a formula that will tell me 26 for 26 total transaction days in February.

[MichelleRoberts]

I got the count to work as it should but now I need a formula to take the 26 and divide the qty to get how many parts per day. The issue is that I want each area to be divided by 26 but because some areas didn't have a shipment in every day, it wants to divide by the number of days where there were transactions in that area. Do you know how that can be accomplished? 

[v-jianpeng-msft · Accepted solution]

Hi, @MichelleRoberts 

Thanks @SivaMani . Have you solved your problem? I read your previous reply, you can create a DAX expression similar to the following:

First, you can create a measure based on the current Table Visual, as shown in the following image:

Use a DAX expression similar to the following:

Here are the results:

How to Get Your Question Answered Quickly

If it does not help, please provide more details with your desired output and pbix file without privacy information (or some sample data) .

Best Regards

Jianpeng Li

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.