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.
Hello,
I'm very new to PowerBI and apologies if something similar has already been asked. I have the following table which displays a Month and Quarter field from a date table named Calendar and a Headcount total field from a Measures table named KPI.
Calendar[Month] | Calendar[Quarter] | KPI[Headcount] |
4/1/2017 | FY18 Q1 End | 167,844 |
5/1/2017 | FY18 Q1 End | 165,929 |
6/1/2017 | FY18 Q1 End | 162,950 |
7/1/2017 | FY18 Q2 End | 161,704 |
8/1/2017 | FY18 Q2 End | 159,783 |
9/1/2017 | FY18 Q2 End | 156,550 |
10/1/2017 | FY18 Q3 End | 155,830 |
11/1/2017 | FY18 Q3 End | 154,418 |
12/1/2017 | FY18 Q3 End | 152,062 |
1/1/2018 | FY18 Q4 End | 151,245 |
2/1/2018 | FY18 Q4 End | 149,279 |
3/1/2018 | FY18 Q4 End | 139,390 |
4/1/2018 | QTD | 137,924 |
5/1/2018 | QTD | 135,758 |
6/1/2018 | QTD | 135,338 |
What I would like to do is display Calendar[Month] & KPI[Headcount] in a bar w/ the ability to drill up to Calendar[Quarter] but instead of showing the aggregate headcount for the Quarter I would like to show the Headcount for the last month in the Quarter. When i currently drill up to Quarter the below results in blue are displayed but would like to show the results in red.
Calendar[Quarter] | KPI[Headcount] | KPI[Headcount] |
FY18 Q1 End | 496,723 | 162,950 |
FY18 Q2 End | 478,037 | 156,550 |
FY18 Q3 End | 462,310 | 152,062 |
FY18 Q4 End | 439,914 | 139,390 |
QTD | 409,020 | 135,338 |
I was thinking this could perhaps be acheived by using IF statement w/ HASONEVALUE function on the Quarter and doing running count on the Month by Quarter and using the last value in the running count but not sure how the running count would work (see working example below).
Headcount New = IF(HASONEVALUE(Calendar[Quarter],
LastMonthInQtrHeadcount,
KPI[Headcount])
Calendar[Month] | Calendar[Quarter] | RunningCount | KPI[Headcount] |
4/1/2017 | FY18 Q1 End | 1 | 167,844 |
5/1/2017 | FY18 Q1 End | 2 | 165,929 |
6/1/2017 | FY18 Q1 End | 3 | 162,950 |
7/1/2017 | FY18 Q2 End | 1 | 161,704 |
8/1/2017 | FY18 Q2 End | 2 | 159,783 |
9/1/2017 | FY18 Q2 End | 3 | 156,550 |
Any help is much appreciated.
Thanks!
Best,
Steve
Solved! Go to Solution.
Thanks Jimmy for you're help. You're reply got me a lot closer and i ended up w/ a slightly different solution. i also have FY Start & End Date and Month Start & End Date fields in my calendar table that I was able to leverage. I created a new calc field in my calendar table called Quarter End Status.
Quarter End Status =
Switch(
True(),
eomonth(Calendar[Month End Date],0) = eomonth(Calendar[Quarter End Date],0),"Yes",
blank())
i was then able to use that in the following measure:
Headcount New =
VAR l_month = CALCULATE(COUNTA('KPIs'[employee_id]),
FILTER('KPIs',
'KPIs'[@ Trend Type] = "HC"))
VAR l_quarter = CALCULATE(COUNTA('KPIs'[employee_id]),
FILTER('KPIs',
'KPIs'[@ Trend Type] = "HC"),
FILTER (
VALUES ( 'Calendar'[Quarter End Status] ),
'Calendar'[Quarter End Status] = "Yes"))
RETURN
SWITCH (
TRUE (),
HASONEVALUE ( 'Calendar'[Month Start Date] ), l_month,
HASONEVALUE ( 'Calendar'[Quarter Start Date] ), l_quarter,
CALCULATE(COUNTA('KPIs'[employee_id]),
FILTER('KPIs',
'KPIs'[@ Trend Type] = "HC")))
Hi scowans,
Create a measure using DAX formula like below:
Result = CALCULATE(MAX(Table1[KPI[Headcount]]]), FILTER(ALLEXCEPT(Table1, Table1[Calendar[Quarter]]]), Table1[Calendar[Month]]] = MAX(Table1[Calendar[Month]]])))
Hope it's helpful to you.
Jimmy Tao
Thanks Jimmy for you're help. You're reply got me a lot closer and i ended up w/ a slightly different solution. i also have FY Start & End Date and Month Start & End Date fields in my calendar table that I was able to leverage. I created a new calc field in my calendar table called Quarter End Status.
Quarter End Status =
Switch(
True(),
eomonth(Calendar[Month End Date],0) = eomonth(Calendar[Quarter End Date],0),"Yes",
blank())
i was then able to use that in the following measure:
Headcount New =
VAR l_month = CALCULATE(COUNTA('KPIs'[employee_id]),
FILTER('KPIs',
'KPIs'[@ Trend Type] = "HC"))
VAR l_quarter = CALCULATE(COUNTA('KPIs'[employee_id]),
FILTER('KPIs',
'KPIs'[@ Trend Type] = "HC"),
FILTER (
VALUES ( 'Calendar'[Quarter End Status] ),
'Calendar'[Quarter End Status] = "Yes"))
RETURN
SWITCH (
TRUE (),
HASONEVALUE ( 'Calendar'[Month Start Date] ), l_month,
HASONEVALUE ( 'Calendar'[Quarter Start Date] ), l_quarter,
CALCULATE(COUNTA('KPIs'[employee_id]),
FILTER('KPIs',
'KPIs'[@ Trend Type] = "HC")))
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 |
---|---|
109 | |
99 | |
77 | |
66 | |
54 |
User | Count |
---|---|
144 | |
104 | |
102 | |
87 | |
64 |