TwinJohnson
New Member
- Joined
- Jul 15, 2021
- Messages
- 11
- Office Version
- 365
- 2011
- Platform
- Windows
Hello,
I'm building a view that captures a snapshot on a particular day. I have a date table that contains all the dates Mon-Sunday from 2019-2030.
On any given day orders are in a status of pick, ship, invoice in which I've applied a flag for each time the status of each order has changed which allows a view of what's happening today. Yesterday we had 101 orders drop, 50 in pack, 50 in ship status, 1 invoiced. Tomorrow the same order may be in the same or different status but cumulatively rolling sums.
What I'm trying to see is on each of the dates Jan 1, 2019-March 28, 2023 (today), count how many orders were in each of these statuses on "that particular day" in the date table (not what status they are today). I've tried several measures and cannot seem to get the snapshots.
Measure:
Open Order = VAR date_to_examine =
MAX('Rolling Date'[DateField])
VAR NUMBER_ORDERS =
CALCULATE(
DISTINCTCOUNT('2020 Log'[Order No]),
KEEPFILTERS(date_to_examine > '2020 Log'[Date of Initial Drop]),
KEEPFILTERS(date_to_examine < '2020 Log'[Date Comp.])
)
RETURN
IF(ISBLANK(NUMBER_ORDERS),0,NUMBER_ORDERS)
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
also tried this measure
SNAPSHOT PICKED NOT SHIPPED =
CALCULATE(
SUM('2020 LOG' [PICKED NOT SHIPPED FLAG]),
FILTER(
ALL ('DATE TABLE'[DateField]),
'DATE TABLE' [DateField] <= MAX( 'DATETABLE'[DateField])
)
)
Thank you,
Tawnee
I'm building a view that captures a snapshot on a particular day. I have a date table that contains all the dates Mon-Sunday from 2019-2030.
On any given day orders are in a status of pick, ship, invoice in which I've applied a flag for each time the status of each order has changed which allows a view of what's happening today. Yesterday we had 101 orders drop, 50 in pack, 50 in ship status, 1 invoiced. Tomorrow the same order may be in the same or different status but cumulatively rolling sums.
What I'm trying to see is on each of the dates Jan 1, 2019-March 28, 2023 (today), count how many orders were in each of these statuses on "that particular day" in the date table (not what status they are today). I've tried several measures and cannot seem to get the snapshots.
Measure:
Open Order = VAR date_to_examine =
MAX('Rolling Date'[DateField])
VAR NUMBER_ORDERS =
CALCULATE(
DISTINCTCOUNT('2020 Log'[Order No]),
KEEPFILTERS(date_to_examine > '2020 Log'[Date of Initial Drop]),
KEEPFILTERS(date_to_examine < '2020 Log'[Date Comp.])
)
RETURN
IF(ISBLANK(NUMBER_ORDERS),0,NUMBER_ORDERS)
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
also tried this measure
SNAPSHOT PICKED NOT SHIPPED =
CALCULATE(
SUM('2020 LOG' [PICKED NOT SHIPPED FLAG]),
FILTER(
ALL ('DATE TABLE'[DateField]),
'DATE TABLE' [DateField] <= MAX( 'DATETABLE'[DateField])
)
)
Thank you,
Tawnee