Forum Discussion
Helper VFlat Line in Cumulative Curve
Hi hackfifi
What I would suggest you do is the following:
Create a measure for your A Value with the example below:
A Total = IF(SUM('Table2'[A]) = 0,BLANK(),SUM('Table2'[A]))The above will always ensure that there is one place to change it, as well as then make it easier for the filter context later.
Then with the above measure you can now create your cumulative curve with the following measure.
Cumulative Actual = Var LNBD = LASTNONBLANK('Table2'[Date],[A Total]) RETURN CALCULATE([A Total],FILTER(ALL('Table2'[Date]), 'Table2'[Date] <= LNBD))- Hi there for your Year and Month values are those coming from the 'Progress Curve' table or the date table?
I think that might be the reason why it is not working as expected.
Helper VGilbertQ - Mate what am i doing wrong here with this "Actual" curve?
As per the PBI Query Data, I have actual until June-17. The Curve stops correctly in June-17, but the "cumulative" calculation does not seem to work. Thanks again.
Cumulative Actual = Var LNBD = LASTNONBLANK('Progress Curve'[Period], [Progress % Actual]) RETURN CALCULATE([Progress % Actual],FILTER(ALL('Progress Curve'[Period]), 'Progress Curve'[Period] <= LNBD))
PBI GraphPBI Query
Super UserAs that might be why it is stopping short.
- hackfifi
Thanks GilbertQ for getting back to me.
So i created a new measure: Var LNBD = LASTNONBLANK('Progress Curve'[Period], [Progress % Actual])
and updated Cumulatve Actual = CALCULATE([Progress % Actual],FILTER(ALL('Progress Curve'[Period]), 'Progress Curve'[Period] <= [Var LNBD]))
Unfortunately No luck.
Cumulative Actual is calculating to be same value Progress % Actual i.e the value is not being calculated as running total.
See table below:
- GilbertQIt appears to be not working as expected. Another option is to hardcode the Var LNBD to see if that works as expected.
The reason for the hardcoding is so that it can eliminate the Cumulative measure is working. - hackfifi
Tried using the below (25th June 2016)
Var LNBD = date(2017,06,25)
- GilbertQOk so to me then it is working as expected because the last date was 25 June 2016, so the line is going as far as that last date?
- hackfifi
Yes, the line has always been working perfectly.
The line "value" is incorrect i.e. it not calculating as cumulative.
The line "value" is as COLUMN D below, but i need the values to be as COLUMN E (cumulative)
- GilbertQOk I can see that thanks. What does the Period column look like?
And when that is compared to the measure, do the Periods match up to the LNBD? - GilbertQOk thanks for that, when you put the above into a table, but this time using the measures for the [Progress % Actual], as well as the Period, and then the ]Cumulatve Actual]?
The only thing that I have always done is to have a Date table and I use the Date table for my cumulative totals to ensure that I always have the dates, even when there is no data. - hackfifi
GilbertQ - Thanks again for your help. I just cant work out why the "Cumulative Actual" Formula doesnt work.
Cumulative Plan & Cumulative Forecast works perfectly.
The formulas used are:
Cumulative Plan =
CALCULATE (
'F2 Hours'[Progress % F2 Plan],
FILTER (
ALLSELECTED('Progress Curve'),
('Progress Curve'[Period] <= MAX ( 'Progress Curve'[Period]))))Cumulative Forecast =
CALCULATE (
'F2 Hours'[Progress % Forecast],
FILTER (
ALLSELECTED('Progress Curve'),
('Progress Curve'[Period] <= MAX ( 'Progress Curve'[Period]))))Cumulative Actual =
CALCULATE(
'F2 Hours'[Progress % Actual],
FILTER(
ALL('Progress Curve'[Period]),
'Progress Curve'[Period] <= [Var LNBD])) - GilbertQHi there for your Year and Month values are those coming from the 'Progress Curve' table or the date table?
I think that might be the reason why it is not working as expected.