Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago

DAX find date when order count exceeded value

I need to find the date when we had 4000 orders so far in a month. Each row is an order. 

 

For example, my data is

 

Each month, I need to return the date that contains the 4000th occurance that month. 

Need Help

5 Replies

  • AlB's avatar
    AlB
    Community Champion
    7 years ago

    Hi Anonymous

     

    Is there an index column or anything else that differentiates rows with the same date? 

    I think you could try somethig like this:

    1. Create a Date table and a relationship with your data table. 

    2. Place Date[Month] in the rows of a matrix visual

    3. Place this measure in values:

     

     

    NthSaleDate =
VAR _N = 4000 //Change this if your want to find an occurrence other than 4000th
RETURN
    FIRSTNONBLANK (
        SELECTCOLUMNS (
            FILTER (
                ADDCOLUMNS (
                    VALUES ( Table1[Date] ),
                    "CountUpToCurrentRow", COUNTROWS ( FILTER ( Table1, Table1[Date] <= EARLIER ( Table1[Date] ) ) )
                ),
                [CountUpToCurrentRow] >= _N
            ),
            "Date", [Date]
        ),
        1
    )

     

    • Anonymous's avatar
      Anonymous
      Not applicable
      7 years ago

      Thank you! 

       

      Is there a way to make a calculated column to show if each order is under the volume limit for the month? Ie if an order was placed before Dec 17th it would be under limit and after Dec 17th it would be over limit? 

      • vanessafvg's avatar
        vanessafvg
        Community Champion
        7 years ago

        Anonymous you could use an if statement assuming you have the volumes already

         

        limitstatus = if(orderVolume < volumeLimit,"under limit", "over limit")

  • Ashish_Mathur's avatar
    Ashish_Mathur
    Super User
    7 years ago

    Hi,

     

    Can you share sample data in an MS Excel file.  Share the link from where i can download that Excel file.