Forecast Sum in Line Chart

[JoaoLucas]

Hi Power Bi Community, 

I'm wondering if it's possible to sum all forecast values projected in a line chart. Because our business is based on forecast analysis, we needed to know the cumulative value for the period we selected in the chart.

I searched the web for this topic but did not find a solution.

*Consider the image as an example

Do you have any suggestions for a solution?

Thanks in advance!

[JoaoLucas]

I realized. But I'm looking for something else. Consider the following scenario:

Make a Line Chart with a 1-year forecast.

I'd like to have a Card that displays the total value of the Forecast generated by the visual.
Is there a Dax Measure that can do it?

[Anonymous]

HI @JoaoLucas,

You can extract the current category and use the current date value to get the previous date value, then you can use these values as forecast values.

If you are confused about coding formula, can you please share some dummy data that keep the raw data structure with expected results? They should help us clarify your scenario and test to coding formula.

How to Get Your Question Answered Quickly  

Regards,

Xiaoxin Sheng

[JoaoLucas]

Thanks for your help! 
You can find the data extracted from the visual here as a sample.

Date	Value	forecastValue	confidenceHighBound	confidenceLowBound
01/01/2018	5560782
01/02/2018	3077646
01/03/2018	4653840
01/04/2018	4166852
01/05/2018	4439928
01/06/2018	4056547
01/07/2018	4074802
01/08/2018	4534955
01/09/2018	3103308
01/10/2018	5365654
01/11/2018	5492045
01/12/2018	6941048
01/01/2019	8064877
01/02/2019	4390242
01/03/2019	5534477
01/04/2019	6432866
01/05/2019	6474449
01/06/2019	5135766
01/07/2019	6921033
01/08/2019	6845774
01/09/2019	6783686
01/10/2019	5975929
01/11/2019	6078076
01/12/2019	5082973
01/01/2020	6326392
01/02/2020	5266328
01/03/2020	7984761
01/04/2020	7365550
01/05/2020	6177771
01/06/2020	7303232
01/07/2020	6530612
01/08/2020	5644700
01/09/2020	6955484
01/10/2020	6755124
01/11/2020	8177077
01/12/2020	6920522
01/01/2021	11401846
01/02/2021	7538232
01/03/2021	8910501
01/04/2021	9681911
01/05/2021	9622167
01/06/2021	8805337
01/07/2021	8589440
01/08/2021	9336723
01/09/2021	9722110
01/10/2021	10424050
01/11/2021	7504885
01/12/2021	7341516
01/01/2022	9840748
01/02/2022	10170544
01/03/2022	13355300
01/04/2022	10935093
01/05/2022	11273224
01/06/2022	11190323
01/07/2022	9705778
01/08/2022	10738521
01/09/2022	13157338
01/10/2022	11310213
01/11/2022	8149857	8149856,835	8149856,835	8149856,835
01/12/2022		10611893,63	12842511,78	8381275,481
01/01/2023		12981816,89	15212439,96	10751193,82
01/02/2023		10543374,88	12774003,35	8312746,412
01/03/2023		12430691,95	14661326,32	10200057,58
01/04/2023		12363177,16	14593817,95	10132536,36
01/05/2023		12031615,68	14262263,44	9800967,916
01/06/2023		11826210,5	14056865,8	9595555,196
01/07/2023		11881125,17	14111788,6	9650461,734
01/08/2023		11941810,75	14172482,93	9711138,574
01/09/2023		12249748,67	14480430,23	10019067,12
01/10/2023		12382181,61	14612873,2	10151490,02
01/11/2023		12097933,86	14328636,17	9867231,551
01/12/2023		12142894,54	14391851,84	9893937,244

[Anonymous]

HI @JoaoLucas,

I'm not so clear how your sample forecast data calculate. As I said, you can use current date to look up previous year data as forecast.

forecast=
VAR currDate =
    MAX ( Table[Date] )
RETURN
    CALCULATE (
        SUM ( Table[Value] ),
        FILTER (
            ALLSELECTED ( Table ),
            [Date]
                = DATE ( YEAR ( currDate ) - 1, MONTH ( currDate ), DAY ( currDate ) )
        )
    )

For high bound and lower bound, you can summary previous records to get the max and min result:

HighBound =
VAR currDate =
    MAX ( Table[Date] )
VAR summary =
    SUMMARIZE (
        FILTER ( ALLSELECTED ( Table ), [Date] < currDate ),
        [Date],
        "Total", SUM ( Table[Value] )
    )
RETURN
    MAXX (
        FILTER ( summary, FORMAT ( [Date], "mmm/dd" ) = FORMAT ( currDate, "mmm/dd" ) ),
        [Total]
    )

LowBound =
VAR currDate =
    MAX ( Table[Date] )
VAR summary =
    SUMMARIZE (
        FILTER ( ALLSELECTED ( Table ), [Date] < currDate ),
        [Date],
        "Total", SUM ( Table[Value] )
    )
RETURN
    MINX (
        FILTER ( summary, FORMAT ( [Date], "mmm/dd" ) = FORMAT ( currDate, "mmm/dd" ) ),
        [Total]
    )

Regards,

Xiaoxin Sheng

[JoaoLucas]

The sample forecast was calculated in line chart visuals using the Algoritheme from Power BI. 

• https://stoneridgesoftware.com/take-advantage-of-forecasting-in-power-bi-with-the-analytics-pane/

The chart has "Date" on the X-Axis and Sales on the Y-Axis. We have each month sales displayed and this PBI function calculates the forecast automatically based on Sales values.

My question is how can i show in a card box the total value for the forecast.
In other words how can i sum the total values in the forecast line generated by PBI (Forecast Value, Upper Bound and Lower Bound

[amitchandak]

@JoaoLucas , I doubt you can do much with this forecast value.

You can create a cumulative visual, in that case forecast will also be cumulative