Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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).
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...
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.
@Anonymous , Not very clear. Can you share sample data and sample output in a table format?
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...
@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:
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
108 | |
98 | |
78 | |
65 | |
60 |
User | Count |
---|---|
148 | |
113 | |
97 | |
84 | |
67 |