Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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
User | Count |
---|---|
106 | |
86 | |
81 | |
73 | |
72 |
User | Count |
---|---|
112 | |
100 | |
98 | |
72 | |
66 |