See if this works for you.
I created a Beginning Month field: DATE(DATETRUNC('month',[Beginning Date]))
Then I broke out the count of days for each pay period into two measure: One for the dates belonging in the Beginning Month:
And one for the dates beginning in the End Month:
In this calculation, I check whether Begin Month < End Month. If so, I count the days from the first day of End Month to the actual Ending Date of the Pay Period.
Then, I wrap this day count in a LOOKUP( ... ,-1) along the Beginning Month field so that the End Month Days will be moved under the next Beginning Month. For Pay Period 4, the 11 days that belong to February should normally fall under a January Begin Month, because January is the Pay Period's begin month. But by using LOOKUP( ...,-1) I've shifted it over a month to February.
Now I put these two calculations together to count all days per Pay Period within a Begin Month:
The ZN() calculation here turns nulls to 0. If either beg month days or end month days is null, this calculation returns null, so ZN() fixes that.
Workbook is attached. I hope this helps.
PayPeriod_2018.1_jvh.twbx 53.8 KB
This is what i want . You are awesome . You saved me. Thank you very much .
Glad it worked, happy to help!