cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Helper II

## New, active and closed Contracts

Hello everybody. I've got a Date Table (Calender) and a Table with IDs, Start date and End date for some Contracts (Data).

I need to answer three different questions pro each Period (fiscal year october - september):

1. how many new contracts  (in this period)

2. how many active contracts (from this and the previus periods)

3. how many closed contracts (in this period)

Now 1 and 3 are easy: I connected Calender[Date] with both [Start Date] and [End Date] from Data and selected the correct relastionship between the Tables.

I am stuck with 2, since I can' t ignore the active relationship and every measure keeps counting the new contracts and not the active ones.

Sample Data / Input:

 ID START END A 16.10.2020 30.08.2021 B 16.10.2020 31.01.2021 C 21.12.2020 31.04.2021 D 13.01.2021 E 08.03.2021 30.06.2021 F 01.09.2021 30.09.2021

Output:

 New Closed Active which IDs should be counted in active Okt. 20 2 0 2 A;B Nov. 20 0 0 2 A;B Dez. 20 1 0 3 A;B;C Jan. 21 1 1 4 A;B;C;D Feb. 21 0 0 3 A;C;D Mrz. 21 1 0 4 A;C;D;E Apr. 21 0 1 4 A;C;D;E Mai. 21 0 0 3 A;D;E Jun. 21 0 1 3 A;D;E Jul. 21 0 0 2 A;D Aug. 21 0 1 2 A;D Sep. 21 1 1 2 D;F

What am I doing wrong? Thank you in advance!

1 ACCEPTED SOLUTION
Super User

Try

Active contracts =
VAR ReferenceDate =
MAX ( 'Calendar'[Date] )
VAR Result =
CALCULATE (
COUNTROWS ( 'Table' ),
REMOVEFILTERS ( 'Date' ),
'Table'[Start date] <= ReferenceDate
&& (
ISBLANK ( 'Table'[End date] )
|| 'Table'[End date] > ReferenceDate
)
)
RETURN
Result
Super User

Try

Active contracts =
VAR ReferenceDate =
MAX ( 'Calendar'[Date] )
VAR Result =
CALCULATE (
COUNTROWS ( 'Table' ),
REMOVEFILTERS ( 'Date' ),
'Table'[Start date] <= ReferenceDate
&& (
ISBLANK ( 'Table'[End date] )
|| 'Table'[End date] > ReferenceDate
)
)
RETURN
Result

Announcements

#### Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

#### Power BI Monthly Update - June 2024

Check out the June 2024 Power BI update to learn about new features.

#### Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.

#### New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

Top Solution Authors
Top Kudoed Authors