cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
scowans
Frequent Visitor

Conditional measure for last month in quarter

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/2017FY18 Q1 End167,844
5/1/2017FY18 Q1 End165,929
6/1/2017FY18 Q1 End162,950
7/1/2017FY18 Q2 End161,704
8/1/2017FY18 Q2 End159,783
9/1/2017FY18 Q2 End156,550
10/1/2017FY18 Q3 End155,830
11/1/2017FY18 Q3 End154,418
12/1/2017FY18 Q3 End152,062
1/1/2018FY18 Q4 End151,245
2/1/2018FY18 Q4 End149,279
3/1/2018FY18 Q4 End139,390
4/1/2018QTD137,924
5/1/2018QTD135,758
6/1/2018QTD135,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 End496,723162,950
FY18 Q2 End478,037156,550
FY18 Q3 End462,310152,062
FY18 Q4 End439,914139,390
QTD409,020135,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]RunningCountKPI[Headcount]
4/1/2017FY18 Q1 End1167,844
5/1/2017FY18 Q1 End2165,929
6/1/2017FY18 Q1 End3162,950
7/1/2017FY18 Q2 End1161,704
8/1/2017FY18 Q2 End2159,783
9/1/2017FY18 Q2 End3156,550

 

Any help is much appreciated.

 

Thanks!

 

Best,

Steve

 

 

1 ACCEPTED 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")))

 

 

View solution in original post

2 REPLIES 2
v-yuta-msft
Community Support
Community Support

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]]])))

捕获.PNG  

 

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")))

 

 

View solution in original post

Helpful resources

Announcements
PBI User Groups

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group!

MBAS on Demand

Microsoft Business Applications Summit sessions

On-demand access to all the great content presented by the product teams and community members! #MSBizAppsSummit #CommunityRocks

MBAS Attendee Badge

Claim Your Badge & Digital Swag!

Check out how to claim yours today!

secondImage

Are You Ready?

Test your skills now with the Cloud Skills Challenge.

Top Solution Authors