Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
Hello! I have a Cumulative Sales (Pink) and want to add my cumulative forecast (Black) so the starting point of the forecast =ending value of sales.
Sales and forecast are in different tables, and it's not an option to join.
How to do it?
Solved! Go to Solution.
Hello @Anonymous
One method would be: find the last nonblank value of Cumulative Sales and add that to the existing Cumulative Forecast.
As an example, if you have:
then the measure could look like this:
Cumulative Forecast Shifted = VAR LastCumulativeSales = CALCULATE ( [Cumulative Sales], LASTNONBLANK ( ALL ( 'Date'[Date] ), [Cumulative Sales] ) ) VAR CumulativeForecast = [Cumulative Forecast] RETURN IF ( NOT ISBLANK ( CumulativeForecast ), LastCumulativeSales + CumulativeForecast )
There may be some tweaking required based on your data model but that's the logic I would use.
Regards,
Owen
Hello @Anonymous
One method would be: find the last nonblank value of Cumulative Sales and add that to the existing Cumulative Forecast.
As an example, if you have:
then the measure could look like this:
Cumulative Forecast Shifted = VAR LastCumulativeSales = CALCULATE ( [Cumulative Sales], LASTNONBLANK ( ALL ( 'Date'[Date] ), [Cumulative Sales] ) ) VAR CumulativeForecast = [Cumulative Forecast] RETURN IF ( NOT ISBLANK ( CumulativeForecast ), LastCumulativeSales + CumulativeForecast )
There may be some tweaking required based on your data model but that's the logic I would use.
Regards,
Owen
User | Count |
---|---|
125 | |
106 | |
99 | |
63 | |
60 |
User | Count |
---|---|
135 | |
116 | |
101 | |
71 | |
61 |