Forum Discussion

COBRIEN's avatar
COBRIEN
Frequent Visitor
5 years ago

Running Total Using a Date Input

Hi,

 

I am trying to create a Line and Clustered Column Chart to represent actual and outstanding cumulative values (Line) and period values (Clustered Columns) for a dataset.  The shared axis represents week ending date (Friday) and is intentionally set to Type = Continuous in the format tab even though my dataset does not have period values for every week ending data point.  I have managed to complete most of what I need but am running into a few issues.

 

The cumulative outstanding line should start where the cumulative actual line ends however if there is a gap in the continuity of the dataset on the week ending where they should meet it is shown as a gap between the lines.  Please see the table below for an example.

 

Week EndingActual PeriodActual CumulativeOutstanding PeriodOutstanding CumulativeActual Cumulative (Desired)Outstanding Cumulative (Desired)

18-Sep-20

88  8 
25-Sep-20210  10 
02-Oct-20    10 
09-Oct-20    10 
16-Oct-20     10
23-Oct-20  1828 28
30-Oct-20  1139 39

 

I have created a measure to calculate the week ending of the previous week Previous Week Ending =

IF(WEEKDAY(TODAY(),2)>5,(TODAY()-(WEEKDAY(TODAY(),2)-5)),(TODAY()+(5-(WEEKDAY(TODAY(),2))-7))).
 

The Actual Cumulative value needs to populate up to the Previous Week Ending date that is calculated in the measure ignoring the fact that there are blank period values.  The Outstanding Cumulative needs to begin the week following the Previous Week Ending measure.

 

The measures for Actual and Outstanding Cumulative are as follows:

 

Actual Cumulative =
    IF(ISBLANK(SUM(Table[Actual Period])),BLANK(),
    CALCULATE(SUM(Table[Actual Period]),
    FILTER(
        ALLSELECTED(Table[Week Ending]),
        ISONORAFTER(Table[Week Ending],MAX(Table[Week Ending]),DESC)
    )
))
 
Outstanding Cumulative =
IF(ISBLANK(SUM(Table[Outstanding Period])),BLANK(),
CALCULATE(
    SUM(Table[Outstanding Period])+SUM(Table[Actual Period]),
    FILTER(
        ALLSELECTED(Table[Week Ending]),
        ISONORAFTER(Table[Week Ending], MAX(Table[Week Ending]), DESC)
    )
))
 
Thanks in advance for any help provided.

2 Replies

  • amitchandak's avatar
    amitchandak
    Super User
    5 years ago

    COBRIEN , Try like

     

    Actual Cumulative =
    IF(max(Table[Week Ending])>=today(),BLANK(),
    CALCULATE(SUM(Table[Actual Period]),
    FILTER(
    ALLSELECTED(Table[Week Ending]),
    ISONORAFTER(Table[Week Ending],MAX(Table[Week Ending]),DESC)
    )
    ))

    Outstanding Cumulative =
    IF(max(Table[Week Ending])<=today(),BLANK(),
    CALCULATE(
    SUM(Table[Outstanding Period])+SUM(Table[Actual Period]),
    FILTER(
    ALLSELECTED(Table[Week Ending]),
    ISONORAFTER(Table[Week Ending], MAX(Table[Week Ending]), DESC)
    )
    ))

    • COBRIEN's avatar
      COBRIEN
      Frequent Visitor
      5 years ago

      Thanks for the reply, unfortunately this did not seem to solve my problem.