Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.

Reply
Anonymous
Not applicable

Running total with 0 values

Hi, 

 

I have this table in my model :

 

DAY_ORDER    SHIFT         WEEKDAY    QUANTITY

1NIGHT_SHIFT_WDMON70
1MORNING_SHIFT_WDMON43
1EVENING_SHIFT_WDMON75
2NIGHT_SHIFT_WDTUE34
2EVENING_SHIFT_WDTUE75
2MORNING_SHIFT_WDTUE96
3NIGHT_SHIFT_WDWED43
3EVENING_SHIFT_WDWED45
3MORNING_SHIFT_WDWED64
4NIGHT_SHIFT_WDTHU0
4EVENING_SHIFT_WDTHU0
4MORNING_SHIFT_WDTHU0
5NIGHT_SHIFT_WDFRI0
5EVENING_SHIFT_WDFRI0
5MORNING_SHIFT_WDFRI0
6NIGHT_SHIFT_WKNDSAT0
6DAY_SHIFT_WKNDSAT0
7NIGHT_SHIFT_WKNDSUN0
7DAY_SHIFT_WKNDSUN0
7NIGHT_SHIFT_WDSUN0




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 :

 
 
running_total.png

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! 




1 ACCEPTED SOLUTION
smpa01
Super User
Super User

@Anonymous You can create an additional measuere as

 

Measure = SUMX('Table','Table'[QUANTITY])
 
and new measure
Measure 2 = IF([Measure]=0,0,[QUANTITY running total in WEEKDAY])
 
test.PNG
Did I answer your question? Mark my post as a solution!
Proud to be a Super User!
My custom visualization projects
Plotting Live Sound: Viz1
Beautiful News:Viz1, Viz2, Viz3
Visual Capitalist: Working Hrs

View solution in original post

3 REPLIES 3
smpa01
Super User
Super User

@Anonymous You can create an additional measuere as

 

Measure = SUMX('Table','Table'[QUANTITY])
 
and new measure
Measure 2 = IF([Measure]=0,0,[QUANTITY running total in WEEKDAY])
 
test.PNG
Did I answer your question? Mark my post as a solution!
Proud to be a Super User!
My custom visualization projects
Plotting Live Sound: Viz1
Beautiful News:Viz1, Viz2, Viz3
Visual Capitalist: Working Hrs
Anonymous
Not applicable

@smpa01 Hey! That's exactly what I was looking for! Thanks for helping!! Much appreciated! 

TomMartens
Super User
Super User

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



Did I answer your question? Mark my post as a solution, this will help others!

Proud to be a Super User!
I accept Kudos 😉
Hamburg, Germany

Helpful resources

Announcements
Microsoft Fabric Learn Together

Microsoft Fabric Learn Together

Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City

PBI_APRIL_CAROUSEL1

Power BI Monthly Update - April 2024

Check out the April 2024 Power BI update to learn about new features.

April Fabric Community Update

Fabric Community Update - April 2024

Find out what's new and trending in the Fabric Community.