COUNT order below specific state and no records between changes

[joshua1990]

Hello!

I have a transaction table that shows me the order, state and the date when a state was changed:

Order Number	State	Value	Date Changed
9998	1	50	01.07.2021
9998	2	50	05.07.2021
9998	3	50	08.07.2021
9999	1	50	05.07.2021
9999	2	50	06.07.2021

This table is linked by a date table.

Now I would like to count the number of order for each date where the state is below 3.

As you can see there are no records between some day since the state was not changed.

How can this be calculated in dax?

[tamerj1 · Accepted solution]

Hi @joshua1990 
Here is a sample file with the solution https://we.tl/t-FABvxI5sFa

# Of Orders (Status < 3) = 
SUMX ( 
    'Table',
    VAR CurrentDate = VALUES ( 'Date'[Date] )
    VAR CurrentState = 
        'Table'[State]
    VAR StartDate = 
        'Table'[Date Changed]
    VAR NextDate = 
        CALCULATE ( MAX ( 'Table'[Date Changed] ), 'Table'[State] = CurrentState + 1, ALLEXCEPT ( 'Table', 'Table'[Order Number] ) )
    VAR EndDate = 
        IF ( ISBLANK ( NextDate ), StartDate, NextDate - 1 )
    VAR CurrentStatusDates =
        CALENDAR ( StartDate, EndDate )
    VAR Result =
        COUNTROWS ( INTERSECT ( CurrentStatusDates, CurrentDate ) )
    RETURN
        IF ( Result > 0 && CurrentState < 3, 1 )
)

[Whitewater100]

Hi:

You can use something like

Count of State < 3 = 

COUNTROWS(FILTER(Table, Table[State] < 3))

You would sub in your table name where I have table. I hope this helps..

[joshua1990]

@Whitewater100 : This will not work. For instance 07.01.2022 I will get now value, right? Because there is no record for 07.01.2022. But order 9998 still has a state below 3, right?

[Whitewater100]

Hi:

Finally, I forgot to attach file. FYI..

https://drive.google.com/file/d/1QbKyOc8gVZFF3kZX0_1KONAhUXo_IKz3/view?usp=sharing 

[Whitewater100]

Hi:

Try thiscalculated column and then a measure. I hope this solves your question..

Calc Col 

Count of State < 3 =

IF('Table'[State] <3,1,BLANK())

Measure

No. Orders ❤️ =

SUM('Table'[Count of State < 3])

[Whitewater100]

Hi:

I don't know how the heart snuck in there!