Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
Hello,
I want to have forecast cumulative line linked with actual cumulative line.I have made two measures-actual cumulative and forecast cumulative,and next step should be to link these two cumulative lines in the same graph...
Can you help me,please?
Thank you!
Best regards.
Solved! Go to Solution.
@Mihai_Iso , Add these two measures and create a new one
m3 = [Forecase Cumm] +[Actual Cumm]
Measures
Actual Date = count(Actual[Actual Finish])
Forecast Date = count(Forecast[Forecast Finish])
Actual cumulative ( stop after last value)
Actual cumulative =
VAR _End = CALCULATE ( MAX ( Actual[Actual Finish] ), ALL ( 'DateTable' ) )
RETURN
IF (
_End < MIN ( 'DateTable'[Date] ),
BLANK (),
CALCULATE(
COUNTA(Actual[Actual Finish]), USERELATIONSHIP(Actual[Actual Finish],'DateTable'[Date]),
FILTER(
ALLSELECTED('DateTable'[Date]),
('DateTable'[Date] <= MAX('DateTable'[Date])))))
Forecast cumulative
Forecast Cumulative =
CALCULATE(
[Forecast Date],
FILTER(
ALLSELECTED('DateTable'[Week no. real]),
ISONORAFTER('DateTable'[Week no. real], MAX('DateTable'[Week no. real]), DESC)
)
)
B_Actual =
var lastactual =
calculate(
MAX(Actual[Actual Finish]),
REMOVEFILTERS(DateTable)
)
var Actuals= [Actual cumulative]
var FutureForecasts=
calculate(
[Forecast Cumulative],
FILTER(
values(DateTable),
DateTable[Date]>lastactual)
)
var Result = Actuals + FutureForecasts
Return
max(DateTable[Date])
As you can see in the table, B_Actual has dates,not values…
Thank you.
best regards,
Mike.
"B_Actual"(I put a wrong description) is in fact another measure which contain actual and forecast cumulative (combined) line.
But as I have explained to you, in case we have data(counting) does not work...work only if we have values(numbers).
Anyone have an idea how to solve this?
Thank you in advance!
If you have dates,not values ("Sales amount" is famous...),it is quite difficult to get the correct results.
Guys,anyone who can help me?
Hi,
Thank you for your advice.But it is still an issue,please find below
I need also to have both cumulative in the graph, to see against cut-off (week 35,in this case).
Hi, be aware that there are no numbers(values), there are dates,counted dates,please see below.Cumulative counted dates by week,for plan,actual and forecast.
User | Count |
---|---|
128 | |
108 | |
99 | |
65 | |
62 |
User | Count |
---|---|
136 | |
113 | |
102 | |
71 | |
60 |