Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now
Hey Guys - Any idea how to get rid of the BLACK FLAT LINE for Actuals? The Actual Curve should stop after TODAY's Date. The data extract is also shown below.
The Formula used for the Actual Curve is:
Cumulative Actual = CALCULATE (
SUM ('Contract Closeout Combined'[A]),
FILTER (
ALLSELECTED('Contract Closeout Combined'),
('Contract Closeout Combined'[Date] <= max('Contract Closeout Combined'[Date]))
))/sum('Total Progress (Forecast)'[Total Progress (Forecast)])
i tried replacing "('Contract Closeout Combined'[Date] <= max('Contract Closeout Combined'[Date]) with Today()"
but that did not work.
Solved! Go to Solution.
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 @hackfifi
This should work below, might need one round bracket
Cumulative Actual = CALCULATE ( SUM ( 'Contract Closeout Combined'[A] ), FILTER ( ALLSELECTED ( 'Contract Closeout Combined' ), ( 'Contract Closeout Combined'[Date] <= LASTNONBLANK ( 'Contract Closeout Combined'[Date], SUM ( 'Contract Closeout Combined'[A] ) ) ) ) ) / SUM ( 'Total Progress (Forecast)'[Total Progress (Forecast)] )
Hi @hackfifi
Apologies Monday morning try this instead
CALCULATE ( SUM ( 'Contract Closeout Combined'[A] ), FILTER ( 'Contract Closeout Combined' , 'Contract Closeout Combined'[Date] <= LASTNONBLANK ( 'Contract Closeout Combined'[Date], SUM ( 'Contract Closeout Combined'[A] ) ) ) ) / SUM ( 'Total Progress (Forecast)'[Total Progress (Forecast)] )
Thanks again @GilbertQ! - but no luck! 🙂
I have attached updated curve and data snippet of that point. Do you think it has anything to do with null/0 values?
Also you will see anything afte 11th June 2017 is 0, as it is TODAY'S date. (data snippet also attached)
Hi @hackfifi
Ok that is because the values are not NULL, but have a zero
This should then work, what I am doing is saying if the value is 0 then make it BLANK, which will then create the right filter context for the LASTNONBLANK
CALCULATE ( IF(SUM ( 'Contract Closeout Combined'[A] ) =0,BLANK(),SUM ( 'Contract Closeout Combined'[A] )), FILTER ( 'Contract Closeout Combined' , 'Contract Closeout Combined'[Date] <= LASTNONBLANK ( 'Contract Closeout Combined'[Date], SUM ( 'Contract Closeout Combined'[A] ) ) ) ) / SUM ( 'Total Progress (Forecast)'[Total Progress (Forecast)] )
Hi @GilbertQ - I think we (you actually) are almost there. The Curve stops on the final data date, but the only issue now is that it is NOT calculating "cumulative"
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))
Thanks @GilbertQ. I will give it a shot
1. Is 'Table2' a new TABLE with "A" column only or do i refer to my existing table 'Contract Closeout Combined'?
2. I have not tried it yet, but is it the correct syntax to have "=" twice?
Cumulative Actual = Var LNBD = LASTNONBLANK('Table2'[Date],[A Total]) RETURN CALCULATE([A Total],FILTER(ALL('Table2'[Date]), 'Table2'[Date] <= LNBD))
Hi @hackfifi
Yes replace 'Table2' with your table name.
Yes the first one is a VARIABLE denoted as Var, so it will work as expected.
Mate - It worked. You are a genius.
I am sure you will hear from me more!
Now all i have to do is understand you formula to learn and pick it up myself!
Cheers Again.
Firoz
@GilbertQ - 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))
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:
Tried using the below (25th June 2016)
Var LNBD = date(2017,06,25)
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)
Thanks again @GilbertQ
Hopefully i understood ur last query correctly. The LNBD works perfectly, as you can see from the below data --> the LNBD is 25/06/2017. The value of Actual after this date is NULL. Hence, i need the cumulative sum of the NON-NULL values
Check out the October 2024 Power BI update to learn about new features.
Learn from experts, get hands-on experience, and win awesome prizes.
User | Count |
---|---|
115 | |
112 | |
105 | |
95 | |
58 |
User | Count |
---|---|
174 | |
147 | |
136 | |
102 | |
82 |