CODEWITHSUNDEEP
powerbidaxpowerquery
Chadi
Chadi

Reputation: 21

Calculate active contracts based on start and end date in Power BI?

My Data looks something like this:

ContractID  Start Date  End Date
1           01.01.2020  23.03.2020
2           15.02.2020  29.07.2020
3           06.06.2020  null

The last contract would be still active. I have a DateTable with the Start Date as the active relationship.

I need the end result too look like this:

Date    Active Contracts
Jan     1
Feb     2
Mar     2
Apr     1
May     1
Jun     2

How should the measure look like?

Thanks in advance!

Upvotes: 1

Views: 1074

Answers (1)

Joao Leal
Joao Leal

Reputation: 5542

Assuming you have a month in your date table

VAR currentMonth = SELECTEDVALUE(MyDataTable[Month value]) --needs to be a number 1 to 12
RETURN CALCULATE(COUNTROWS(MyDataTable), 
    ALL(DateTable), 
    MONTH(MyDataTable[Start Date]) >= currentMonth, 
    ISBLANK(MyDataTable[End Date]) || MONTH(MyDataTable[End Date]) <= currentMonth)

Upvotes: 4

Related Questions