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 Experts,
I have a Department tables wit h departments data , some departments have data since 2018 Jan, but some only have from 2019 Jan.
Requirement is that even when a department does not have existance in a 2018 the graph should display date from 2018 Jan .Is this possible?
Sample Data
Departments | Headcount | Report Date |
Department 1 | 200 | Jan-18 |
Department 1 | 250 | Jan-19 |
Department 2 | 20 | Jan-18 |
Department 2 | 23 | Jan-19 |
Department 3 | 30 | Jan-19 |
Thanks
DJ
Solved! Go to Solution.
Hi @Anonymous
I would say it is possible, but your specification is a bit short. How do you want to visualize this? With department on axis, or just the total of all departments and time? Do you just want to see the values for january 2018 and january 2019, or do you want to se the values between as well? Which value should be used for a department which has no value in january 2018, 0 or the value from january 2019?
Here is a version that will work if you have department on axis or legend, and want to use the 2019 value if 2018 is blank.
number of employees number from next year = VAR firstYear = CALCULATE ( MIN ( Department[Report Date] ); ALL ( 'date'[Report Date] ) ) VAR firstValue = CALCULATE ( SUM ( Department[Headcount] ); FILTER ( ALL ( 'date'[Report Date] ); 'date'[Report Date] = firstYear ) ) RETURN IF ( ISBLANK ( SUM ( Department[Headcount] ) ); firstValue; SUM ( Department[Headcount] ) )
Cheers,
Sturla
Hi @Anonymous
I would say it is possible, but your specification is a bit short. How do you want to visualize this? With department on axis, or just the total of all departments and time? Do you just want to see the values for january 2018 and january 2019, or do you want to se the values between as well? Which value should be used for a department which has no value in january 2018, 0 or the value from january 2019?
Here is a version that will work if you have department on axis or legend, and want to use the 2019 value if 2018 is blank.
number of employees number from next year = VAR firstYear = CALCULATE ( MIN ( Department[Report Date] ); ALL ( 'date'[Report Date] ) ) VAR firstValue = CALCULATE ( SUM ( Department[Headcount] ); FILTER ( ALL ( 'date'[Report Date] ); 'date'[Report Date] = firstYear ) ) RETURN IF ( ISBLANK ( SUM ( Department[Headcount] ) ); firstValue; SUM ( Department[Headcount] ) )
Cheers,
Sturla
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 |
---|---|
113 | |
99 | |
82 | |
70 | |
60 |
User | Count |
---|---|
149 | |
114 | |
107 | |
89 | |
67 |