Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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 =
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:
@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)
)
))
Thanks for the reply, unfortunately this did not seem to solve my problem.
User | Count |
---|---|
57 | |
21 | |
19 | |
18 | |
16 |
User | Count |
---|---|
85 | |
80 | |
52 | |
37 | |
22 |