New Row With Calculated Values

[Anonymous]

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 😄 

Thanks in advance

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

[Anonymous]

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.

[amitchandak]

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

[Anonymous]

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... 

[Greg_Deckler]

@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