Preforming Excel Functions in Power Bi

Is the original GOAL of 2603 a fixed number, does it have a source or is it stored in a table...?
EDIT: I see the initial goal is the actual total sum of Total Products, so I'll take that for the calculations.

Ok, the issue is that if you have blanks in the fact table, if you sort or reference these rows in a calculation, by default they are "sorted" as the lowest value (or "date". This is relevant because in order to achieve the Goal measure, we need the cumulative value for Current Improvements. One way to solve this is to assign dates to the blank rows (I've simply allotted them the date value of the max date in the fact table + 1 year -this will make sense when we create the date table. Basically by adding dates for the next year based on the max date which actually has values assigned to a date, we can then include blank fields in the Date Table which can be assigned to the original blank dates. Since these dates are "in the future", we can use the sorting to make calculations and display them as the last values. Make sense?).

You can do this in Power Query using the following code:

 

let
  Source = Table.FromRows(
    Json.Document(
      Binary.Decompress(
        Binary.FromText(
          "RY3LCQAxCER78RyIn9W4tSzpv41VgvEywnsO833ANNEmIxMMII5A2ONwL+4ewXoET8IuZMOkDFKaZLSyI22szKMSqXbNezvovV8zKXhdnm+GmEeaaMSCvX8=",
          BinaryEncoding.Base64
        ),
        Compression.Deflate
      )
    ),
    let
      _t = ((type nullable text) meta [Serialized.Text = true])
    in
      type table [DATE = _t, #"Total Products" = _t, #"Current Improvements" = _t]
  ),
  #"Changed Type" = Table.TransformColumnTypes(
    Source,
    {{"DATE", type date}, {"Total Products", Int64.Type}, {"Current Improvements", Int64.Type}}
  ),
  #"Sorted Rows" = Table.Sort(#"Changed Type", {{"DATE", Order.Ascending}}),
  #"Changed Type1" = Table.TransformColumnTypes(#"Sorted Rows", {{"DATE", type date}}),
  #"Calculated Latest" = List.Max(#"Changed Type1"[DATE]),
  #"Added Conditional Column" = Table.AddColumn(
    #"Changed Type1",
    "Custom",
    each if [DATE] = null then Date.AddYears(#"Calculated Latest", 1) else [DATE]
  ),
  #"Removed Columns" = Table.RemoveColumns(#"Added Conditional Column", {"DATE"}),
  #"Renamed Columns" = Table.RenameColumns(#"Removed Columns", {{"Custom", "New Date"}}),
  #"Changed Type2" = Table.TransformColumnTypes(#"Renamed Columns", {{"New Date", type date}})
in
  #"Changed Type2"

 

 

 

If you feel more comfortable using DAX, you can add a new calculated column to the fact table using:

 

New Date =
VAR _MXDate =
    MAX ( 'fTable DAX'[DATE] )
VAR _Final =
    DATE ( YEAR ( _MXDate ) + 1, MONTH ( _MXDate ), DAY ( _MXDate ) )
RETURN
    IF ( ISBLANK ( 'fTable DAX'[DATE] ), _Final, 'fTable DAX'[DATE] )

 

 

In either case, you need to create the Date Table and its relationship based on the "New Date" field. Create the Date Table using (I'm referencing the table created on Power Query, so addapt the code if you have used DAX to create the extra dates in the fact Table):

Date Table =
VAR _MaxYear =
    YEAR ( MAX ( 'fTable PQ'[New Date] ) ) // Returns the max date in the fact table, which is the dates we have assigned to the blank rows
RETURN
    ADDCOLUMNS (
        CALENDAR ( MIN ( 'fTable PQ'[New Date] ), MAX ( 'fTable PQ'[New Date] ) ),
        "MonthNum", IF ( YEAR ( [Date] ) = _MaxYear, BLANK (), MONTH ( [Date] ) ),
        // ALL the IF clauses are to return a blank value if the date is in the max year
        "Month", IF ( YEAR ( [Date] ) = _MaxYear, BLANK (), FORMAT ( [Date], "MMM" ) ),
        "QuarterNum", IF ( YEAR ( [Date] ) = _MaxYear, BLANK (), QUARTER ( [Date] ) ),
        "Quarter",
            IF ( YEAR ( [Date] ) = _MaxYear, BLANK (), "Q" & QUARTER ( [Date] ) ),
        "YearSort", YEAR ( [date] ),
        // we only need this extra row to sort the following "Year" row, which is relevant to place blanks at the bottom of the visual
        "Year", IF ( YEAR ( [Date] ) = _MaxYear, BLANK (), YEAR ( [Date] ) )
    )

Note the blank values which will allow to show blanks in the visual

(Note the blank fields to be used in the visual)

Sort the dates in ascending order; sort the "Year" column by the "YearSort" column

Now you can create the measures. I have created SUM measures for Total Products and Current Improvements. The Goal measure is as follows:

 

 

Goal =
VAR _InitialGoal =
    CALCULATE ( SUM ( 'fTable PQ'[Total Products] ), ALL ( 'fTable PQ' ) ) // Returns the sum for all Total Products
VAR _CumulativeImprovements =
    CALCULATE (
        SUM ( 'fTable PQ'[Current Improvements] ),
        FILTER (
            ALL ( 'Date Table' ),
            'Date Table'[Date] <= MAX ( 'Date Table'[Date] )
        )
    ) // Returns the cumulative sum of Current Improvements in ascending order by the Date Table[Date] field
RETURN
    IF (
        ISBLANK ( SUM ( 'fTable PQ'[Current Improvements] ) ),
        BLANK (),
        _InitialGoal - _CumulativeImprovements
    )

 

 

 

and you will get:

 

I've attached the sample PBIX file