Forum Discussion
Running total with 0 values
Hi,
I have this table in my model :
DAY_ORDER SHIFT WEEKDAY QUANTITY
| 1 | NIGHT_SHIFT_WD | MON | 70 |
| 1 | MORNING_SHIFT_WD | MON | 43 |
| 1 | EVENING_SHIFT_WD | MON | 75 |
| 2 | NIGHT_SHIFT_WD | TUE | 34 |
| 2 | EVENING_SHIFT_WD | TUE | 75 |
| 2 | MORNING_SHIFT_WD | TUE | 96 |
| 3 | NIGHT_SHIFT_WD | WED | 43 |
| 3 | EVENING_SHIFT_WD | WED | 45 |
| 3 | MORNING_SHIFT_WD | WED | 64 |
| 4 | NIGHT_SHIFT_WD | THU | 0 |
| 4 | EVENING_SHIFT_WD | THU | 0 |
| 4 | MORNING_SHIFT_WD | THU | 0 |
| 5 | NIGHT_SHIFT_WD | FRI | 0 |
| 5 | EVENING_SHIFT_WD | FRI | 0 |
| 5 | MORNING_SHIFT_WD | FRI | 0 |
| 6 | NIGHT_SHIFT_WKND | SAT | 0 |
| 6 | DAY_SHIFT_WKND | SAT | 0 |
| 7 | NIGHT_SHIFT_WKND | SUN | 0 |
| 7 | DAY_SHIFT_WKND | SUN | 0 |
| 7 | NIGHT_SHIFT_WD | SUN | 0 |
I'm using the following measure to calculate a running total :
QUANTITY running total in WEEKDAY =
CALCULATE(
SUM('Table'[QUANTITY ]);
FILTER(
CALCULATETABLE(
SUMMARIZE('Table'; 'Table'[DAY_ORDER ] ; 'Table'[WEEKDAY]);
ALLSELECTED('Table')
);
ISONORAFTER(
'Table'[DAY_ORDER ]; MAX('Table'[DAY_ORDER ]); DESC;
'Table'[WEEKDAY]; MAX('Table'[WEEKDAY]); DESC
)
)
)
Which gives me this chart :
Which gives me this chart :
So far so good. Now let's say today is Wednesday, which means the the values for days between Thursday and Sunday has not been updated yet(I'm retring this data from a database). I need that chart to show "0" value on those days until of course the day in question arrives and my table is updated.
Is there any way I can pull it off with Dax/Power Query ?
PS: I know I could use conditional formating to make days THU - SUN "disappear but I would like to have "0" values being displaye in the chart. Think of it as a running total with dynamic reset.
Any help is appreciated!
Is there any way I can pull it off with Dax/Power Query ?
PS: I know I could use conditional formating to make days THU - SUN "disappear but I would like to have "0" values being displaye in the chart. Think of it as a running total with dynamic reset.
Any help is appreciated!
Anonymous You can create an additional measuere as
Measure = SUMX('Table','Table'[QUANTITY])and new measureMeasure 2 = IF([Measure]=0,0,[QUANTITY running total in WEEKDAY])
3 Replies
Anonymous You can create an additional measuere as
Measure = SUMX('Table','Table'[QUANTITY])and new measureMeasure 2 = IF([Measure]=0,0,[QUANTITY running total in WEEKDAY])smpa01 Hey! That's exactly what I was looking for! Thanks for helping!! Much appreciated!
hey Anonymous ,
you can use WEEKDAY(TODAY() , ...) to find out the daynumber of the weekday, incorporate into your statement and return BLANK() if DAY_ORDER is greater than WEEKDAY(TODAY() , ...).
Hopefully, this provides some new ideas that will help to tackle your challenge.
Regards,
Tom
