Forum Discussion
Distinct count depending on multiple lines
Hi H3nning ,
Based on your problems, here are my answers.
First I create three tables as you mentioned.
Then I write a measure.
Formatted Date = FORMAT('Date'[Date], "d.M.yyyy")Finally I write another measure and you will get what you want.
MEASURE = VAR CurrentDate = SELECTEDVALUE ( 'Date'[Formatted Date] ) RETURN CALCULATE ( DISTINCTCOUNT ( Contract[Customer] ), FILTER ( 'Contract', Contract[Start] <= CurrentDate && Contract[End] >= CurrentDate ) )Best Regards
Yilong Zhou
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi H3nning ,
Based on your problems, here are my answers.
First I create three tables as you mentioned.
Then I write a measure.
Formatted Date = FORMAT('Date'[Date], "d.M.yyyy")
Finally I write another measure and you will get what you want.
MEASURE =
VAR CurrentDate =
SELECTEDVALUE ( 'Date'[Formatted Date] )
RETURN
CALCULATE (
DISTINCTCOUNT ( Contract[Customer] ),
FILTER (
'Contract',
Contract[Start] <= CurrentDate
&& Contract[End] >= CurrentDate
)
)
Best Regards
Yilong Zhou
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
That only counts the numer of customers but not those older than one year. But i could manage that by writing the lowest Starting date of any contract on each customer:
LowestStart=Min(Start)
And bringing that to the contract:
LowestStartOfCustomer=Related(Customer[LowestStart])
That i can include i a datediff clause in the conditions of your measure. So thank you!