Time intelligence: Dim Date table and table with start and end date

[xale19]

Hi,

I have two tables: one table with contracts that includes contract information like price, start date oft the contract and end date of the contract. On the basis of reading some blogs about time intelligence I created a DimDate Table. This table has an active relationship with Start Date and an inactive relationship with End Date. Goal of my report is to count the active contracts, the new contracts and the cancellations. In addition to that I want to see the changes to the last years. It would be perfect if I could illustrate this in one visualization. But my problem is that I don't have a the same basis of dates.

My DAX forumla are:

cancelation = CALCULATE(DISTINCTCOUNT(contracts[Customer]);FILTER(contracts;contracts[totalprice in Euro]>0);Filter(contracts;contracts[End Date].[Date]<Today()))

new contracts = CALCULATE(DISTINCTCOUNT(contracts[Customer]);FILTER(DateTable;DateTable[Date].[Date]=DateTable[Date].[Date]);FILTER(contracts;contracts[totalprice in Euro]>0))

active contracts = CALCULATE(DISTINCTCOUNT(contracts[Customer]);FILTER(contracts;contracts[Start Date]<=CALCULATE(Max(DateTable[Date])));Filter(contracts;contracts[End Date]>=CALCULATE(MIN(DateTable[Date])));Filter(contracts;contracts[totalprice in Euro]>0))

Thank you for your help!

[v-sihou-msft]

@xale19

I'm still not clear about your logic for new contracts and cancelation. For your active contracts, you can write your measure like below:

active contracts =
CALCULATE (
    DISTINCTCOUNT ( contracts[Customer] ),
    FILTER (
        contracts,
        contracts[Start Date] <= LASTDATE ( DateTable[Date] )
            && contracts[End Date] >= FIRSTDATE ( DateTable[Date] )
            && contracts[totalprice in Euro] > 0
    )
)

Please also refer to article below:

Counting “Active” Rows in a Time Period: Guest Post from Chris Campbell

Regards,

[xale19]

Hi @v-sihou-msft,

thank you for your answer.

I had a similar solution at active contracts before. It worked until I managed a relationship between DateTable and Start Date.

Do you have any ideas why my time intelligence isn't working?

Do you have any ideas for cancellation and new contracts?

You're right, especially for new contracts there is no real logic at this formula... but the results are well.

UPDATE:

now I removed the relationship again and active contracts is working now, but now I have the wrong results for new contracts. 

best regards