Calculate MAX Status per Sales Order for Selected Day

[joshua1990]

Hello all,

I am currently trying to develop a report that shows how many open sales order there were for a given day/period.

As visualization I would like to use a simple bar chart, which shows how many open sales orders there were for the respective day / week.
The structure of the data is as follows:

Sales Order	Value	Status	Date
1001	50	1	01.01.2022
1001	60	6	02.01.2022
1002	60	6	03.01.2022

Open sales requisitions have a status < 6.
How can I find out how many orders had a status <6 for the respective period?

[tamerj1 · Accepted solution]

@joshua1990 

The following code is more simple

Open Sales =
CALCULATE (
    DISTINCTCOUNT ( Table[Sales] ),
    REMOVEFILTERS ( 'Calendar' ),
    Table[Date] <= MAX ( 'Calendar'[Date] ),
    Table[Status] < 6
)

[tamerj1]

Hello again

In case you are using the date column from the same table in the visual then you can try

Open Sales =
CALCULATE ( DISTINCTCOUNT ( Table[Sales] ), FILTER ( ALL ( Table ), Table[Date] <= MAX(Table[Date]) && Table [Status] < 6 ))

[tamerj1]

Hi @joshua1990 
do you have date table? Any relationships?

[joshua1990]

@tamerj1 : Yes, I have a calendar table.

[tamerj1]

Then please try

Open Sales =
CALCULATE ( DISTINCTCOUNT ( Table[Sales] ), REMOVEFILTERS('Calendar'), FILTER ( ALL ( Table ), Table[Date] <= MAX('Calendar'[Date]) && Table [Status] < 6 ))