Preforming Excel Functions in Power Bi

[Anonymous]

Hi, 

I have this simple excel function and I am very new to DAX and measures and was wondering if there is a simple way to preform this task in power bi. In my excel I am taking the previous cell value and subtracting it from a column in a different row. I would like to repeat this process for multiple rows. I was wondering if anybody could help me with this dax formula.

Information about Data:

Current Improvements: measure made by subtracting two different columns 

My goal is for every new calcuated total the projected total value will update. 

Thanks!

[PaulDBrown]

Where are the blank rows coming from? where from are the values 2063 (goal) and the last blank line?

	Total Products	Current Improvements
Blank	1615	127

So far I have this...

[PaulDBrown]

Please provide sample data which reflects the structure and nature of your actual data

[Anonymous]

Hello, 

This is the structure of the actual data, some of the date rows are missing a date because no date was probided, but I have the other data such as total product. Therefore, they are blank. The photos above are actual screenshots of my table in my dashboard. I have used measures, and do not columns of my measures when I am in power queary. I am trying to calculate my numbers in the "Goal" column, 

[PaulDBrown]

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

[Anonymous]

For Year and Quarter, these are pulled from my date column 

Current Improvements= Measure(Column 1 - Column 2) 

DATE	Total Products	Current Improvements	GOAL
			2603
21-Jun	12	0	2603-0
21-Aug	88	25	2603-25
22-Jan	122	63	2578-63
22-Apr	178	33	2515-33
22-Sep	453	56	2482-56
22-Sep	108	0	2426-0
22-Oct	27	0	2426-0
Blank	1615	137	2426-127
	2603		2289