Running Total Using a Date Input

COBRIEN — Thu, 15 Oct 2020 18:24:32 GMT

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 Ending	Actual Period	Actual Cumulative	Outstanding Period	Outstanding Cumulative	Actual Cumulative (Desired)	Outstanding Cumulative (Desired)
18-Sep-20	8	8			8
25-Sep-20	2	10			10
02-Oct-20					10
09-Oct-20					10
16-Oct-20						10
23-Oct-20			18	28		28
30-Oct-20			11	39		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.

Re: Running Total Using a Date Input

amitchandak — Fri, 16 Oct 2020 02:40:16 GMT

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)
)
))

Re: Running Total Using a Date Input

COBRIEN — Fri, 16 Oct 2020 10:55:22 GMT

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

topic Re: Running Total Using a Date Input in DAX Commands and Tips

Running Total Using a Date Input

Re: Running Total Using a Date Input

Re: Running Total Using a Date Input