Conditional measure for last month in quarter

[Anonymous]

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

[v-yuta-msft]

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

[Anonymous · 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")))