Forum Discussion

xale19's avatar
xale19
Frequent Visitor
8 years ago

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

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 t...
Need Help
v-sihou-msft's avatar
v-sihou-msft
Icon for Microsoft Employee rankMicrosoft Employee
8 years ago

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's avatar
    xale19
    Frequent Visitor
    8 years ago

    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