Running total up to max Value

Question

Greetings,&nbsp;I have a weekly report so I created a Date Hierarchy with weeks.This filters all my visuals BUT I need to report the running total up to my week max date (I already created the measure [THISWEEKMAX]).&nbsp;I tried to get this through this measure:PROGRESS MAXDATE = CALCULATE(&nbsp;&nbsp;&nbsp;&nbsp;SUM('110-Actual SCH'[% Labor]),&nbsp;&nbsp;&nbsp;&nbsp;FILTER(&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;ALLSELECTED('701-AUX FECHA'),&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;ISONORAFTER('701-AUX FECHA'[Fecha], [THISWEEKMAX], DESC)&nbsp;&nbsp;&nbsp;&nbsp;))&nbsp;With no results.Any help?Thank you all.

pauldbrown · Answer

Try:
PROGRESS MAXDATE =
IF(MAX('701-AUX FECHA'[FECHA]) &lt; =[THISWEEKMAX],
CALCULATE(
&nbsp;&nbsp;&nbsp;&nbsp;SUM('110-Actual SCH'[% Labor]),
&nbsp;&nbsp;&nbsp;&nbsp;FILTER(
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;ALLSELECTED('701-AUX FECHA'),
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; '701-AUX FECHA'[Fecha] &lt;= MAX('701-AUX FECHA'[Fecha])
&nbsp;&nbsp;&nbsp;&nbsp;)
))

pauldbrown · Answer

It's hard to follow without seeing the data. Any chance you coulp share some sample data (non-confidential which you can recreate in Excel for example)?

luzeta_39 · Answer

Thanks Paul! This still filters by the MINWEEKDAY thus the cummulative visual shows the period value. I think I'll give up and filter that visual by another slicer.

pauldbrown · Answer

Can you post a depiction of the expected output? I'm no too sure what you are after and what the issue is

luzeta_39 · Answer

&nbsp;&nbsp;The bottom slicer is WEEK Number.&nbsp;Based on that date the visual "Period Plan" calculates the progress (it's a calculated column):% Labor = DIVIDE('100-BaseLine'[Period],CALCULATE(SUM('100-BaseLine'[Period]),FILTER('100-BaseLine','100-BaseLine'[Material/Labor]="Labor")))Its ok&nbsp;The problem is in Cumm Plan. I want to show the total progress up to the max week date (measure):SCHPLANxMAXWEEKDAY = IF(MAX('701-AUX FECHA'[FECHA]) &lt;=[THISWEEKMAX],CALCULATE(SUM('100-BaseLine'[% Labor]),FILTER(ALLSELECTED('701-AUX FECHA'),'701-AUX FECHA'[Fecha] &lt;= MAX('701-AUX FECHA'[Fecha]))))&nbsp;While THISWEEKMAX is a measure calculated this way: THISWEEKMAX = CALCULATE(MAX('701-AUX FECHA'[Fecha]),FILTER('701-AUX FECHA','701-AUX FECHA'[WEEK]=[THISWEEKNUM]))&nbsp;I think the problem is that THISWEEKMAX is a measure and not a single number.&nbsp;All tables have relationships so I don't this there is any issue.&nbsp;I think I stumbled into myself and maybe its simpler just to add a slicer thats defines the Cumm date.&nbsp;Thank you for your help.&nbsp;

Forum Discussion

Running total up to max Value

6 Replies

Related content

Running Totals

Running total

Tableau to PowerBI Running Totals Issue

SumX? - cumulative running total

Dynamic running Total