New Row With Calculated Values

Question

Hi,&nbsp;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).&nbsp;Since I need to calculate the difference, I created a new table and inserted that new row with the symbol "∆"&nbsp;&nbsp;Table 2 = UNION(VALUES(Sheet1[Date]),ROW("Date","∆"))​&nbsp;and then I used the formula:&nbsp;&nbsp;&nbsp;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]))&nbsp;So, what I'm looking for is something like this:&nbsp;CompanyDateN accidentsA01/12/20182A01/12/20194A∆100B01/12/20180B01/12/20190B∆-C01/12/20185C01/12/20190C∆-&nbsp;Instead, I only get the numbers in front of the dates, and no calculated value or even a line for the&nbsp;∆.&nbsp;Is it even possible what I'm trying to achieve? Should I make the calculation in excel and be done with it?Help 😄&nbsp;Thanks in advance&nbsp;&nbsp;Note: I created a sample PBI file but I don't know how to upload it...&nbsp;

greg_deckler · Answer

Anonymous&nbsp;- 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.
&nbsp;
So, Company and Date in the Rows hierachy and then a measure that follows the principles of MM3TR&amp;R:
https://community.powerbi.com/t5/Quick-Measures-Gallery/Matrix-Measure-Total-Triple-Threat-Rock-amp-Roll/m-p/411443#M150

amitchandak · Answer

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

anonymous · Answer

Sample data:CompanyDateN accidentsDays offA01/01/2017215A01/02/2017427A01/03/2017427A01/04/2017427A01/05/2017427A01/06/2017427A01/07/2017427A01/08/2017427A01/09/2017427A01/10/2017427A01/11/2017427A01/12/2017427A01/01/201800A01/02/201800A01/03/201800A01/04/201800A01/05/201800A01/06/201800A01/07/201800A01/08/2018217A01/09/2018217A01/10/2018217A01/11/2018217A01/12/2018217A01/01/201900A01/02/201900A01/03/201900A01/04/201900A01/05/201900A01/06/201900A01/07/201910A01/08/201910A01/09/201910A01/10/201920A01/11/201930A01/12/201940A01/01/202000A01/02/202010A01/03/202020A01/04/202020A01/05/202020A01/06/202020A01/07/202030B01/01/201700B01/02/201700B01/03/201700B01/04/201700B01/05/201700B01/06/201700B01/07/201715B01/08/201715B01/09/201715B01/10/201715B01/11/201715B01/12/201715B01/01/201800B01/02/201800B01/03/201800B01/04/201800B01/05/201800B01/06/201800B01/07/201800B01/08/201800B01/09/201800B01/10/201800B01/11/201800B01/12/201800B01/01/201900B01/02/201900B01/03/201900B01/04/201900B01/05/201900B01/06/201900B01/07/201900B01/08/201900B01/09/201900B01/10/201900B01/11/201900B01/12/201900B01/01/202000B01/02/202000B01/03/202000B01/04/202000B01/05/202000B01/06/202000B01/07/202000C01/01/201700C01/02/2017112C01/03/2017112C01/04/2017112C01/05/2017112C01/06/2017112C01/07/2017112C01/08/2017112C01/09/2017112C01/10/2017112C01/11/2017112C01/12/2017112C01/01/201800C01/02/201822C01/03/2018410C01/04/2018517C01/05/2018517C01/06/2018517C01/07/2018517C01/08/2018517C01/09/2018517C01/10/2018517C01/11/2018517C01/12/2018517C01/01/201900C01/02/201900C01/03/201900C01/04/201900C01/05/201900C01/06/201900C01/07/201900C01/08/201900C01/09/201900C01/10/201900C01/11/201900C01/12/201900C01/01/202000C01/02/202010C01/03/202010C01/04/202010C01/05/202010C01/06/202010C01/07/202010&nbsp;Desired output:CompanyDateN accidentsDays offA01/12/2018217A01/12/201940A∆100-B01/12/201800B01/12/201900B∆--C01/12/2018517C01/12/201900C∆--&nbsp;I think I'm trying to achieve a pretty reasonable thing here...&nbsp;

anonymous · Answer

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.&nbsp;&nbsp;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]&nbsp;&nbsp;After that i build the matrix table visual with DateDIFF and other fact table columns.&nbsp;Hope it helps you if you have questions feel free to ask.&nbsp;

Forum Discussion

New Row With Calculated Values

4 Replies

Related content

DAX for calculating retroactive values

PowerBI - Matrix table, hide values used only for a visual calculation

Total value missing in calculated column

MoM calculation

Commitment value calculating incorrectly - DAX Logic

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

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