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.
I have 1 table with date, employee and Salary columns like below.
Date | Employee | Salary |
4/1/2023 | Liza | 100 |
4/1/2023 | Maria | 150 |
4/1/2023 | Susan | 200 |
4/2/2023 | Liza | 150 |
4/2/2023 | Maria | 250 |
4/2/2023 | Susan | 125 |
4/3/2023 | Liza | 125 |
4/3/2023 | Maria | 150 |
4/3/2023 | Susan | 100 |
4/4/2023 | Liza | 125 |
4/4/2023 | Maria | 100 |
4/4/2023 | Susan | 205 |
4/5/2023 | Liza | 150 |
4/5/2023 | Maria | 200 |
4/5/2023 | Susan | 200 |
My goal is to calculate the difference of salary between 2 dates - 2 slicer dates.
Example:
Slicer Date 1 = April 1
Slicer Date 2 = April 5
Then I will create a measure to calculate for the difference (Slicer Date 2 - Slicer Date 1) and the visual should look like:
Employee | April 1 | April 5 | Difference |
Liza | 100 | 150 | 50 |
Maria | 150 | 200 | 50 |
Susan | 200 | 200 | 0 |
I can't make this one to work. Any idea if this is possible?
Solved! Go to Solution.
Hi @jhobzvel ,
I created a sample pbix file(see the attachment), please check if that is what you want.
1. Create two separated date dimension tables as below
2. Create two slicers with the date field from the above 2 date dimension tables
3. Create a measure as below to get the values
Difference =
VAR _date1 =
SELECTEDVALUE ( 'Slicer1'[Date] )
VAR _date2 =
SELECTEDVALUE ( 'Slicer2'[Date] )
VAR _salary1 =
CALCULATE (
SUM ( 'Table'[Salary] ),
FILTER ( 'Table', 'Table'[Date] = _date1 )
)
VAR _salary2 =
CALCULATE (
SUM ( 'Table'[Salary] ),
FILTER ( 'Table', 'Table'[Date] = _date2 )
)
RETURN
IF (
ISINSCOPE ( 'Table'[Date] ),
IF (
SELECTEDVALUE ( 'Table'[Date] ) IN { _date1, _date2 },
SUM ( 'Table'[Salary] ),
BLANK ()
),
_salary2 - _salary1
)
4. Create a matrix visual as below screenshot
Best Regards
Hi @jhobzvel ,
I created a sample pbix file(see the attachment), please check if that is what you want.
1. Create two separated date dimension tables as below
2. Create two slicers with the date field from the above 2 date dimension tables
3. Create a measure as below to get the values
Difference =
VAR _date1 =
SELECTEDVALUE ( 'Slicer1'[Date] )
VAR _date2 =
SELECTEDVALUE ( 'Slicer2'[Date] )
VAR _salary1 =
CALCULATE (
SUM ( 'Table'[Salary] ),
FILTER ( 'Table', 'Table'[Date] = _date1 )
)
VAR _salary2 =
CALCULATE (
SUM ( 'Table'[Salary] ),
FILTER ( 'Table', 'Table'[Date] = _date2 )
)
RETURN
IF (
ISINSCOPE ( 'Table'[Date] ),
IF (
SELECTEDVALUE ( 'Table'[Date] ) IN { _date1, _date2 },
SUM ( 'Table'[Salary] ),
BLANK ()
),
_salary2 - _salary1
)
4. Create a matrix visual as below screenshot
Best Regards
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 |
---|---|
110 | |
109 | |
89 | |
76 | |
66 |
User | Count |
---|---|
126 | |
112 | |
99 | |
82 | |
73 |