count of days by month within a date range

[Anonymous]

Help! I cannot figure this out. I have a table to records all with differnt start and end dates ( these are contracts all are 365 days) contracts can start in any month of the year and span a whole year. I have a daily rate value and I am trying to find how many days a contract will be valid in Dec 2023, Jan 2024, Feb 2024, and March 2024. I cannot figure out how to do this.

EX:

record 1  contract start 10/15/2023 - 10/14/2024

record 2 start date 12/15/2023 - 11/14/2024

record 3 start date 2/5/2024 - 2/4/2025

I need to create new columns so that show the count of days in Dec 2023, Jan 2024, Feb 2024, March 2024

expecation:

record	dec 2023	jan 2024	feb 2024	mar 2024
1	31	30	29	31
2	15	30	29	31
3	0	0	24	31

[v-weiyan1-msft · Accepted solution]

Hi @Anonymous ,

Based on the sample and description you provided, Please try code as below to create Calculated Column.

Dec2023 = 
VAR dec_s = DATE(2023,12,1)
VAR dec_e = DATE(2023,12,31)
Return
SWITCH(TRUE(),
Contract[StartDate] < dec_s, DATEDIFF(dec_s,dec_e,DAY)+1,
AND(
    'Contract'[StartDate] < dec_e,
    'Contract'[StartDate] > dec_s
),
DATEDIFF(('Contract'[StartDate]),dec_e,DAY),
'Contract'[StartDate] > dec_e,0
)

The rest of the code is similar, please refer to the attachment.

Result is as below.

In addition, you can also use the method to create a measure, the code is as follows.

Measure = 
var _contarctstart=MAX('Contract'[StartDate])
var _DataTableS_Date=SELECTEDVALUE('Data Table'[S_Date])
var _DataTableE_Date=MAX('Data Table'[E_Date])
return
SWITCH(TRUE(),
_contarctstart < _DataTableS_Date, DATEDIFF(_DataTableS_Date,_DataTableE_Date,DAY)+1,
AND(
   _contarctstart > _DataTableS_Date,
    _contarctstart < _DataTableE_Date
),
DATEDIFF(_contarctstart,_DataTableE_Date,DAY),
_contarctstart > _DataTableE_Date,0
)

Result is as below.

Best Regards,
Yulia Yan

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Ashish_Mathur]

Hi,

Similar problem solved in the attached PBI file.

Hope this helps.

[amitchandak]

@Anonymous , seem like very similar to

Measure way
Power BI Dax Measure- Allocate data between Range: https://youtu.be/O653vwLTUzM

Power BI: HR Analytics - Employees as on Date : https://youtu.be/e6Y-l_JtCq4
https://community.powerbi.com/t5/Community-Blog/HR-Analytics-Active-Employee-Hire-and-Termination-trend/ba-p/882970
Power BI HR Active Employee Tenure Bucketing, and Hired, Terminated, and Active employees: https://youtu.be/fvgcx8QLqZU