cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Helper II
Helper II

Show order per month but count them just one time

Hello,

 

I want to show the quantity of new orders per month in a diagram.

 

I have a column with order numbers, some of them appear multiple times, in different months and also in the same month.

In the diagram, the order number should be counted just one time. And that should be the time when the number apperead for the first time.

 

I used the command "DISTINCTCOUNT" but i think it counts a number twice when it appears in another/ the next month.

 

Thanks for any thoughts about that!

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Super User VI
Super User VI

Re: Show order per month but count them just one time

You can use an expression like this in your measure to get the distinct count of first-time orders

 

First Time Orders =
VAR __thisdate =
    MIN ( Table[Date] )
RETURN
    COUNTROWS (
        FILTER (
            DISTINCT ( Table[OrderNumber] ),
            ISBLANK (
                CALCULATE ( COUNTROWS ( Table ), ALL ( Table[Date] ), Table[Date] < __thisdate )
            )
        )
    )

 

If this works for you, please mark it as the solution.  Kudos are appreciated too.  Please let me know if not.

Regards,

Pat





Did I answer your question? Please mark my post as a solution! Kudos are also appreciated!

Proud to be a Super User!




View solution in original post

2 REPLIES 2
Highlighted
Super User VI
Super User VI

Re: Show order per month but count them just one time

You can use an expression like this in your measure to get the distinct count of first-time orders

 

First Time Orders =
VAR __thisdate =
    MIN ( Table[Date] )
RETURN
    COUNTROWS (
        FILTER (
            DISTINCT ( Table[OrderNumber] ),
            ISBLANK (
                CALCULATE ( COUNTROWS ( Table ), ALL ( Table[Date] ), Table[Date] < __thisdate )
            )
        )
    )

 

If this works for you, please mark it as the solution.  Kudos are appreciated too.  Please let me know if not.

Regards,

Pat





Did I answer your question? Please mark my post as a solution! Kudos are also appreciated!

Proud to be a Super User!




View solution in original post

Highlighted
Helper II
Helper II

Re: Show order per month but count them just one time

Thank you, this worked out for me! 🙂

Helpful resources

Announcements
Upcoming Events

Upcoming Events

Wondering what events you could join or have an event to promote yourself? Check out our Upcoming Events.

Upcoming Events

Community Summit North America – Join Online!

Join this community-driven Power Platform digital event for unbiased support and problem-solving.

Experience what’s next for Power BI

Join us for an in-depth look at the new Power BI features and capabilities at the free Microsoft Business Applications Launch Event.

Community Blog

Community Blog

Visit our Community Blog for articles, guides, and information created by fellow community members.

Top Solution Authors