PivotTable from DateTable + ContractTable

[OlaSa]

Hello,

This is my first post here.
I try to learn DAX and can't figure out how to Count the number of Active Contracts by Market.
Could anyone give me a hint ?

Kind regards,
Ola


3 tables (Contracts, Repairs, Dates):
1. Contracts contains ; ContractNo, StartDate, EndDate, Market
2. Repairs contains ; Date, ContractNo, RepairAmount
3. Dates contains only ; Date

2 joins:
Contracts[ContractNo] --> Repairs[ContractNo]
Repairs[Date] --> Dates[Date]


New column create; Dates[Active Contract]
=COUNTAX(FILTER(VALUES(Contracts), AND(Contracts[Start date]<=Dates[Date], Dates[Date]<=Contracts[End date])), Contracts[Contract No])
--> Active contract by Date

 

[ImkeF]

Welcome OlaSa,

try this:
=CALCULATE(COUNT(Contracts[Contract No]), FILTER(Contracts;[Date]<=Contracts[End date] && [Date]>=Contracts[Start date]))

Imke