Summing fortnightly granularity data into monthly

[Anonymous]

Hi all, 

I'm relatively new to Power BI and i'm a bit stuck on something. 

I have a report level date slicer that is on a fortnightly pay period selection. However, I want some of the data on that report to be shown at a monthly granularity. At the moment, the data is at a fortnightly granularity. 

I want to sum up all headcounts for the month in which the fortnightly period exists in. For example, if i select 15/04/19, i want it to sum up all the headcount for month of April 2019. 

So far i'm making a new colum and this is what i have: 









But surely there is a better way to do it. I also have to sum up headcount at a monthly level for age groups and this method doesn't seem to work.

For example, i have age groups: <20, 20-30, 30-40, 40-50, 50+ and i have the associated headcount next to it for each pay period fortnight. But i want to be able to sum up headcount for each age bucket monthly and show it on a bar chart. 

Thank you in advance. Really appreciate any help i can get. 

[Anonymous]

Hi @parry2k ,

Sure, let me organize a file free of confidential data and i'll upload it here again. 

Thanks. 

[parry2k]

@Anonymous Please share data in excel using onedrive/google drive to get your answer quickly. Remove any sensitive information before sharing.