Monthly sum of headcount using fortnight silcers

[Anonymous]

Hi All,

Sorry i've been bombarding with so many questions lately. Still very new to Power BI and DAX. 

So i'm running into a few problems getting a monthly sum of headcount. 

I have a few report-level slicers on my report and one of those slicers is a date slicer (which is on a fortnight pay period basis). The issue i'm having is, i want to sum up the headcount for the previous month to when the slicer was selected. 

For example, if i select fortnight period 11/05/2019, then i want it to return monthly headcount from 1/04/2019 to 30/04/2019. The output should be the same if i select fortnight period 25/05/2019 because it should still calculate headcount for the month of 1/04/2019-30/04/2019. 

The catch is, i can't change the date slicer into month because other visuals on the report will need to be on a fortnightly granularity. However, i need to make it so that when a user selects a fortnight period, it automatically goes back to the previous month and gets monthly sum. 

I also need it to work with the other slicers. So for example, i need to be able to select Location on my slicer as well as the date (among other slicer selection options), and see the monthly sum for those specific areas. 

I've attached the Excel sheet as well as PBIX file. If anyone could help, that would be so great. Thanks everyone!

[Anonymous · Accepted solution]

Hi there.

Mate, I give you a piece of good advice: CHANGE THE MODEL. The one you have right now does not look the way it should, certainly it's not optimal. Please aim at the STAR SCHEMA first and foremost. Please do not overuse the cross-filtering feature because IT'S VERY DANGEROUS and you run the risk of producing numbers nobody will understand, including you.

The model is messy like hell at the moment 😞

The golden rule of data modeling is: All fact tables (that should only store metrics and keys to dimensions) should be connected to the dimensions via foreign keys and no fact table should directly join to a different fact table; they can only be joined to shared dimensions. Please stick to this requirement and you'll be safe. Otherwise... you'll be sorry. Special treatment should be given to the Date table(s) that should also be MARKED as such in the model. This is in order that time-intelligence functions work correctly. You should NEVER, EVER, EVER rely on the automatic feature of Date table creation in Power BI. It's there to only build up your sloppiness and accelerate the rate of making mistakes.

You've been warned.

Having said all that, if your DimDate table is a proper Date table, then the measure you want would (most likely) be:

[Full Time Headcount for Prev Month] =
var __payPeriodEnding = SELECTEDVALUE( DimDate[Date] )
var __payPeriodEndingIsOK = NOT ISBLANK( __payPeriodEnding )
var __sumForPrevMonth =
	CALCULATE(
		-- Just change the measure here and you'll get
		-- the versions you want.
		[Full Time Headcount],
		-- This will only work if DimDate is a PROPER DATE TABLE.
		PREVIOUSMONTH( DimDate[Date] )
	)
return
	IF (
		__payPeriodEndingIsOK,
		__sumForPrevMonth
	)

My advice is to CHANGE THE MODEL. Once again... But it's your call. I've told you what can go wrong.

Best

Darek

[Anonymous]

Hi  @Anonymous , if you don't mind i had one last question.

I want to change the solution so that when i select a period (i.e. 31/03/19), it takes me to the last pay period of the previous month, so in this case, period ending 25th Feb 2019, and sums headcount for that particular pay period. 

Instead of summing for the entire previous month, i want it to only sum for the last pay period of the previous month only.  

I used the same thing but added LASTDATE(PREVIOUSMONTH(DimDate[payPeriodEnd]) to return the last pay period date of the previous month and then calculate sum for that pay period. 

It's working but i'm scared that there's a catch so i wanted to ask you if that is what you would do. Thank you!

[Anonymous]

Hi.

First of all, whatever you do - TEST. Test the good, the bad and the boundaries.

I don't quite know what this means

LASTDATE(PREVIOUSMONTH(DimDate[payPeriodEnd])

but the functions involved will only work correctly with a proper Date table and if the column used---payPeriodEnd---stores contiguous dates. If the above gives you the correct time frames for your calculation (and this is something you can test as well easily creating some debugging measures), then you're all good. If you have to apply some additional logic besides what the functions give you, you certainly can; just add some more filters to your CALCULATE. Just always remember that the time-intel functions work on a date column of a Date/Calendar table that has to have contiguous dates covering all the years you have in your model.

Best

Darek

[Anonymous]

Thanks @Anonymous once again! Definitely will take that onboard and change my model!