SelectedValue in measure to return previous month data

[hnguyen76]

(Cannot Find Previous Post)

Good morning!

I am working on an HR Analystics report and I'm trying to tie some monthly numbers. I have a month slicer that users can choose and I have a table where I want the results to show current month's employee status count and previous month's employee status count. I have a date table created from the MIN and MAX of my fact table, HC. There's also a relationship between the fact table and date table.

When I take a table and manually input the months that I want I get the correct numbers but when I'm trying to make it dynamic based on slicer options I'm falling short. I believe the issue is if current month's slicer has no value in that specific category, ie "retirement", it does not get aggrigated.

In the image I have two tables side-by-side. The left numbers are from a flat table that doesn't interact with any filters / slicers. The right table has a measure that tries to calculate for previous month's data, "October".

My current measure is as follows:

October = 
// Grab Current Month Slicer From Date Table
var mySelection = SELECTEDVALUE('Date'[Date])
// Grab Last Month's Date Based On Slicer From Date Table
var Previous = DATEADD(FILTER(LASTDATE('Date'[Date]), 'Date'[Date] = mySelection), -1, MONTH)
// Lookup YearMonth from DateTable Corresponding To Previous Date
var myLookUp = LOOKUPVALUE('Date'[YM], 'Date'[Date], Previous)
// Count Number of Core From Previous Month
var DoCalc = CALCULATE(COUNT(HC[Core HC]), FILTER(ALL('Date'), 'Date'[YM] = myLookUp))
RETURN
IF(ISBLANK(DoCalc), 0, DoCalc)

If you see, I'm retrieving almost all the data except "Retirement" since there's no one retiring in the month of November. If there's a retirement in the current month's selection the measure works.

Any help would be appreciated! Thanks

[SIH007_1]

Hi, I have a slicer filtering month dates. I need the date selected in the slicer and the month previous to the selected. Took me some time but the solution is easier:
To calculate the previous month:

VAR SelPreviousMonth = Date(Year(SELECTEDVALUE(Calendar[EndMonth])), MONTH(SELECTEDVALUE(Calendar[EndMonth])), 1)-1) 
//my slicer refers to the end of each month, so the variable first calculates the 1st of the selected date, subtracting 1 from this number returns the end of the previous month

For the beginning of the month I would do this:

VAR SelPreviousMonthEnd = Date(YEAR(SELECTEDVALUE(Calendar[StartMonth])), MONTH(SELECTEDVALUE(Calendar[StartMonth])), 1)-1)
//this calculates the last day of the previous month, no matter if new year
VAR SelPreviousMonthStart = Date(YEAR(SelPreviousMonthEnd),MONTH((SelPreviousMonthEnd),1)
//With this variable you just set the calculated date to the first of the previous month

[hnguyen76]

Good morning!

I am working on an HR Analystics report and I'm trying to tie some monthly numbers. I have a month slicer that users can choose and I have a table where I want the results to show current month's employee status count and previous month's employee status count. I have a date table created from the MIN and MAX of my fact table, HC. There's also a relationship between the fact table and date table. 

When I take a table and manually input the months that I want I get the correct numbers but when I'm trying to make it dynamic based on slicer options I'm falling short. I believe the issue is if current month's slicer has no value in that specific category, ie "retirement", it does not get aggrigated.  

In the image I have two tables side-by-side. The left numbers are from a flat table that doesn't interact with any filters / slicers. The right table has a measure that tries to calculate for previous month's data, "October".

My current measure is as follows:

October = 
// Grab Current Month Slicer From Date Table
var mySelection = SELECTEDVALUE('Date'[Date])
// Grab Last Month's Date Based On Slicer From Date Table
var Previous = DATEADD(FILTER(LASTDATE('Date'[Date]), 'Date'[Date] = mySelection), -1, MONTH)
// Lookup YearMonth from DateTable Corresponding To Previous Date
var myLookUp = LOOKUPVALUE('Date'[YM], 'Date'[Date], Previous)
// Count Number of Core From Previous Month
var DoCalc = CALCULATE(COUNT(HC[Core HC]), FILTER(ALL('Date'), 'Date'[YM] = myLookUp))
RETURN
IF(ISBLANK(DoCalc), 0, DoCalc)

If you see, I'm retrieving almost all the data except "Retirement" since there's no one retiring in the month of November. If there's a retirement in the current month's selection the measure works.

Any help would be appreciated! Thanks

[v-frfei-msft]

Hi @hnguyen76,

Could you please share your pbix to me? Please upload the file to One Drive and share the link here.

Reagrds,

Frank

[hnguyen76 · Accepted solution]

Hi Frank! Thanks for the support but I had figured it out. I ended up manipulating the slicer values to retrieve YM. Here's my measure if anyone else needs it:

01_PreviousMonth = 
var MonthNum = IF(SELECTEDVALUE('Date'[Date].[Month]) <> "", SELECTEDVALUE('Date'[Date].[MonthNo]), -1)
var GetYear = IF(SELECTEDVALUE('Date'[Date].[Year]) > 0, FORMAT(SELECTEDVALUE('Date'[Date].[Year]), "####"), -1)
var Previous = MonthNum - 1
var Transform = 
    SWITCH(TRUE(),
        Previous > 1, Previous & GetYear,
        Previous = 1 && MonthNum = 2, Previous & GetYear,
        12 & GetYear - 1) 
var DoCalc = CALCULATE([Employee Group selection], ALL('Date'), HC[YM] = Transform)
return
IF(ISBLANK(DoCalc), 0, DoCalc)