Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

The Power BI DataViz World Championships are on! With four chances to enter, you could win a spot in the LIVE Grand Finale in Las Vegas. Show off your skills.

Reply
hnguyen76
Resolver II
Resolver II

SelectedValue in measure to return previous month data

(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.

 

cap_1.PNG
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

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

View solution in original post

4 REPLIES 4
SIH007_1
Frequent Visitor

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
Resolver II
Resolver II

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.  

 

cap_1.PNG
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
Community Support
Community Support

Hi @hnguyen76,

 

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

 

Reagrds,

Frank

Community Support Team _ Frank
If this post helps, then please consider Accept it as the solution to help the others find it more quickly.

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)

Helpful resources

Announcements
Feb2025 Sticker Challenge

Join our Community Sticker Challenge 2025

If you love stickers, then you will definitely want to check out our Community Sticker Challenge!

Jan NL Carousel

Fabric Community Update - January 2025

Find out what's new and trending in the Fabric community.