Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more.
Get startedGrow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric 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
Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.
Ask questions in Eventhouse and KQL, Eventstream, and Reflex.
User | Count |
---|---|
84 | |
83 | |
64 | |
60 | |
55 |
User | Count |
---|---|
179 | |
108 | |
105 | |
71 | |
70 |