Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
I have the below measure...
The measure works great if the table includes the "Attrition Date", etc.columns, and eiter displays the "First Day Workforce or "0". But I just want to display the rows where the dates match and not the rows with 0.
Solved! Go to Solution.
The Last Period Date is picking the last date of Dec 31st where the Attrition table has values which are the begining of each month. So essentially you are comparing 31 Dec 2023 to 01 Dec 2023.
Try this calculated column...
Test 4 =
var _lastDatePeriodTable =
SELECTCOLUMNS('Date Table', "_startOfMonth", STARTOFMONTH('Date Table'[Date]))
var _lastDatePeriod =
MAXX(_lastDatePeriodTable, [_startOfMonth])
var _vTable =
FILTER(Attrition, Attrition[Attrition Date] = _lastDatePeriod)
return
MINX(_vTable, [Last Day Workforce])
Proud to be a Super User! | |
Test 3 =
var _firstDatePeriod =
CALCULATE(MIN('Date Table'[Date]),ALLSELECTED('Date Table'[Date]))
var _vTable =
FILTER(Attrition, Attrition[Attrition Date] = _firstDatePeriod)
return
MINX(_vTable, [First Day Workforce])
Something like this might get you pointed in the right direction.
Proud to be a Super User! | |
Thanks and that seems to do the trick 👍
I then also wanted to get the "Last Day Workforce" figures (in Blue)...
and simply changed your to code to (in Red)...
Test 3 =
var _LastDatePeriod =
CALCULATE(MAX('Date Table'[Date]),ALLSELECTED('Date Table'[Date]))
var _vTable =
FILTER(Attrition, Attrition[Attrition Date] = _LastDatePeriod)
return
MAXX(_vTable, [Last Day Workforce])
but its not displaying any figures
What am I missing?
Is anyone able to assit with getting the "Last Date Workforce" values, similar to the "First Date Workforce" code? as tried various things, but cant figure it out.
Thanks in advance.
The Last Period Date is picking the last date of Dec 31st where the Attrition table has values which are the begining of each month. So essentially you are comparing 31 Dec 2023 to 01 Dec 2023.
Try this calculated column...
Test 4 =
var _lastDatePeriodTable =
SELECTCOLUMNS('Date Table', "_startOfMonth", STARTOFMONTH('Date Table'[Date]))
var _lastDatePeriod =
MAXX(_lastDatePeriodTable, [_startOfMonth])
var _vTable =
FILTER(Attrition, Attrition[Attrition Date] = _lastDatePeriod)
return
MINX(_vTable, [Last Day Workforce])
Proud to be a Super User! | |
Stupid me, should have spotted that. So thanks very much for taking the time to help me and its really appreciated. You scripts work great.
User | Count |
---|---|
94 | |
83 | |
78 | |
75 | |
66 |
User | Count |
---|---|
115 | |
105 | |
93 | |
65 | |
60 |