Forum Discussion

LuZeta_39's avatar
LuZeta_39
Frequent Visitor
4 years ago

Running total up to max Value

Greetings,   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 measu...
Need Help
PaulDBrown's avatar
PaulDBrown
Icon for Community Champion rankCommunity Champion
4 years ago

Try:

PROGRESS MAXDATE =
IF(MAX('701-AUX FECHA'[FECHA]) < =[THISWEEKMAX],
CALCULATE(
    SUM('110-Actual SCH'[% Labor]),
    FILTER(
        ALLSELECTED('701-AUX FECHA'),
      '701-AUX FECHA'[Fecha] <= MAX('701-AUX FECHA'[Fecha])
    )
))
  • LuZeta_39's avatar
    LuZeta_39
    Frequent Visitor
    4 years ago

    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's avatar
      PaulDBrown
      Icon for Community Champion rankCommunity Champion
      4 years ago

      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's avatar
        LuZeta_39
        Frequent Visitor
        4 years ago

         

         

        The bottom slicer is WEEK Number.

         

        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
         
        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]) <=[THISWEEKMAX],
        CALCULATE(
        SUM('100-BaseLine'[% Labor]),
        FILTER(
        ALLSELECTED('701-AUX FECHA'),
        '701-AUX FECHA'[Fecha] <= MAX('701-AUX FECHA'[Fecha])
        )
        ))
         
        While THISWEEKMAX is a measure calculated this way:
        THISWEEKMAX = CALCULATE(MAX('701-AUX FECHA'[Fecha]),FILTER('701-AUX FECHA','701-AUX FECHA'[WEEK]=[THISWEEKNUM]))
         
        I think the problem is that THISWEEKMAX is a measure and not a single number. 
        All tables have relationships so I don't this there is any issue.
         
        I think I stumbled into myself and maybe its simpler just to add a slicer thats defines the Cumm date.
         
        Thank you for your help.