cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Anonymous
Not applicable

## New Row With Calculated Values

Hi,

So, I'm trying to build a table with the company name, date, the number of accidents (and other variables, but let's simplify) and a calculated formula between the 2 dates I select (for which I added a filter because each month I need to change that).

Since I need to calculate the difference, I created a new table and inserted that new row with the symbol "∆"

``Table 2 = UNION(VALUES(Sheet1[Date]),ROW("Date","∆"))​``

and then I used the formula:

``````Measure =
Var MaxYear = MAX('Table 2'[Date])
Var MinYear = MIN('Table 2'[Date])
Var diff = CALCULATE(SELECTEDVALUE(Sheet1[N accidents]),'Table 2'[Date]=MaxYear)-CALCULATE(SELECTEDVALUE(Sheet1[N accidents]),'Table 2'[Date]=MinYear)
Var final = (DIVIDE(diff*100,(CALCULATE(SELECTEDVALUE(Sheet1[N accidents]),'Table 2'[Date]=MinYear)),"-"))
return SWITCH(SELECTEDVALUE('Table 2'[Date]),"∆",final,SELECTEDVALUE(Sheet1[N accidents]))``````

So, what I'm looking for is something like this:

 Company Date N accidents A 01/12/2018 2 A 01/12/2019 4 A ∆ 100 B 01/12/2018 0 B 01/12/2019 0 B ∆ - C 01/12/2018 5 C 01/12/2019 0 C ∆ -

Instead, I only get the numbers in front of the dates, and no calculated value or even a line for the ∆.

Is it even possible what I'm trying to achieve? Should I make the calculation in excel and be done with it?

Help 😄

Note: I created a sample PBI file but I don't know how to upload it...

4 REPLIES 4
Anonymous
Not applicable

Hi, I did a project similar to your needs. In my scenario company needs to get value diferences between selected dates.My measures is like 3 and 2 of them calculates the min and max of selected date, other one is for getting the difference values by substracting them. Maybe it can light you up 🙂 I pasted my measures below.

``````DatesValueMin = CALCULATE(SUM('ValueTable'[Actuals]),
DATESBETWEEN( Dates[Date],
MIN(Dates[Date]) -30,
MIN(Dates[Date])))``````
``````DatesValueMax= CALCULATE(SUM('ValueTable'[Actual]),
DATESBETWEEN( Dates[Date],
MAX(Dates[Date]) -30,
MAX(Dates[Date])))``````
``DateDIFF = [DatesValueMax]-[DatesValueMin]``

After that i build the matrix table visual with DateDIFF and other fact table columns.

Hope it helps you if you have questions feel free to ask.

Super User

@Anonymous , Not very clear. Can you share sample data and sample output in a table format?

Anonymous
Not applicable

Sample data:

 Company Date N accidents Days off A 01/01/2017 2 15 A 01/02/2017 4 27 A 01/03/2017 4 27 A 01/04/2017 4 27 A 01/05/2017 4 27 A 01/06/2017 4 27 A 01/07/2017 4 27 A 01/08/2017 4 27 A 01/09/2017 4 27 A 01/10/2017 4 27 A 01/11/2017 4 27 A 01/12/2017 4 27 A 01/01/2018 0 0 A 01/02/2018 0 0 A 01/03/2018 0 0 A 01/04/2018 0 0 A 01/05/2018 0 0 A 01/06/2018 0 0 A 01/07/2018 0 0 A 01/08/2018 2 17 A 01/09/2018 2 17 A 01/10/2018 2 17 A 01/11/2018 2 17 A 01/12/2018 2 17 A 01/01/2019 0 0 A 01/02/2019 0 0 A 01/03/2019 0 0 A 01/04/2019 0 0 A 01/05/2019 0 0 A 01/06/2019 0 0 A 01/07/2019 1 0 A 01/08/2019 1 0 A 01/09/2019 1 0 A 01/10/2019 2 0 A 01/11/2019 3 0 A 01/12/2019 4 0 A 01/01/2020 0 0 A 01/02/2020 1 0 A 01/03/2020 2 0 A 01/04/2020 2 0 A 01/05/2020 2 0 A 01/06/2020 2 0 A 01/07/2020 3 0 B 01/01/2017 0 0 B 01/02/2017 0 0 B 01/03/2017 0 0 B 01/04/2017 0 0 B 01/05/2017 0 0 B 01/06/2017 0 0 B 01/07/2017 1 5 B 01/08/2017 1 5 B 01/09/2017 1 5 B 01/10/2017 1 5 B 01/11/2017 1 5 B 01/12/2017 1 5 B 01/01/2018 0 0 B 01/02/2018 0 0 B 01/03/2018 0 0 B 01/04/2018 0 0 B 01/05/2018 0 0 B 01/06/2018 0 0 B 01/07/2018 0 0 B 01/08/2018 0 0 B 01/09/2018 0 0 B 01/10/2018 0 0 B 01/11/2018 0 0 B 01/12/2018 0 0 B 01/01/2019 0 0 B 01/02/2019 0 0 B 01/03/2019 0 0 B 01/04/2019 0 0 B 01/05/2019 0 0 B 01/06/2019 0 0 B 01/07/2019 0 0 B 01/08/2019 0 0 B 01/09/2019 0 0 B 01/10/2019 0 0 B 01/11/2019 0 0 B 01/12/2019 0 0 B 01/01/2020 0 0 B 01/02/2020 0 0 B 01/03/2020 0 0 B 01/04/2020 0 0 B 01/05/2020 0 0 B 01/06/2020 0 0 B 01/07/2020 0 0 C 01/01/2017 0 0 C 01/02/2017 1 12 C 01/03/2017 1 12 C 01/04/2017 1 12 C 01/05/2017 1 12 C 01/06/2017 1 12 C 01/07/2017 1 12 C 01/08/2017 1 12 C 01/09/2017 1 12 C 01/10/2017 1 12 C 01/11/2017 1 12 C 01/12/2017 1 12 C 01/01/2018 0 0 C 01/02/2018 2 2 C 01/03/2018 4 10 C 01/04/2018 5 17 C 01/05/2018 5 17 C 01/06/2018 5 17 C 01/07/2018 5 17 C 01/08/2018 5 17 C 01/09/2018 5 17 C 01/10/2018 5 17 C 01/11/2018 5 17 C 01/12/2018 5 17 C 01/01/2019 0 0 C 01/02/2019 0 0 C 01/03/2019 0 0 C 01/04/2019 0 0 C 01/05/2019 0 0 C 01/06/2019 0 0 C 01/07/2019 0 0 C 01/08/2019 0 0 C 01/09/2019 0 0 C 01/10/2019 0 0 C 01/11/2019 0 0 C 01/12/2019 0 0 C 01/01/2020 0 0 C 01/02/2020 1 0 C 01/03/2020 1 0 C 01/04/2020 1 0 C 01/05/2020 1 0 C 01/06/2020 1 0 C 01/07/2020 1 0

Desired output:

 Company Date N accidents Days off A 01/12/2018 2 17 A 01/12/2019 4 0 A ∆ 100 - B 01/12/2018 0 0 B 01/12/2019 0 0 B ∆ - - C 01/12/2018 5 17 C 01/12/2019 0 0 C ∆ - -

I think I'm trying to achieve a pretty reasonable thing here...

Super User

@Anonymous - Honestly it seems to me you are going about this all wrong. Seems like you should put your data into a matrix visualization and just make sure that the totals displayed within the hierarchy are correct.

So, Company and Date in the Rows hierachy and then a measure that follows the principles of MM3TR&R:

https://community.powerbi.com/t5/Quick-Measures-Gallery/Matrix-Measure-Total-Triple-Threat-Rock-amp-Roll/m-p/411443#M150

Become an expert!: Enterprise DNA
External Tools: MSHGQM
Latest book!:
Power BI Cookbook Third Edition (Color)

DAX is easy, CALCULATE makes DAX hard...