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.
(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
Solved! Go to 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)
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
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
Hi @hnguyen76,
Could you please share your pbix to me? Please upload the file to One Drive and share the link here.
Reagrds,
Frank
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)
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 |
---|---|
96 | |
93 | |
82 | |
70 | |
64 |
User | Count |
---|---|
118 | |
106 | |
93 | |
79 | |
72 |