Forum Discussion
Cumulative Distinct Count For selected Dates
Hi,
I have a table that has property listings everyday, I have a measure that already gets CUMULATIVE count of number of active properties on whole table/site on Last Day of month. I need to create a measure that gives me DISTINCT count of listings for selected month. I tried many methods not sure where I am getting wrong.
Current Measure :
Active Listings:=CALCULATE(
DISTINCTCOUNT('listing History'[ListingInstance]),('Listing History'[ListingStatus]="US" || 'Listing History'[ListingStatus]="AC")
,ALL('Date Reported')
,FILTER
(
ALL('Listing History'[ExpiryDate])
,[ExpiryDate] > MAX('Date Reported'[DateValue])
)
,FILTER
(
ALL('Listing History'[EffectiveEndDate])
,[EffectiveEndDate] >= MAX('Date Reported'[DateValue])
)
,FILTER
(
ALL('Listing History'[ReportedDate_SID])
,[ReportedDate_SID] <= MAX('Date Reported'[Date_SID])
)
)This is my output: I need to know How many listings where unique in that month.
7 Replies
please Download pbix file here Listings.pbix
Hi Anonymous
I went through the pbix file.
I created a relationship between Listing History[ReportedDate_SID] and DateReported[Date_SID] using Manage Relationship under the modelling tab.
I Created a simple measure
01 TestActiveListings =
CALCULATE (
DISTINCTCOUNT ( 'Listing History'[ListingInstance] ),
( 'Listing History'[ListingStatus] = "US"
|| 'Listing History'[ListingStatus] = "AC" )
)Created a slicer by Year and Month Number from Date Reported table. And could see the result shown properly.
I am not aware of the logic you have built for your measure Activity Listing.
If this works for you mark this as solution and also give Kudos
Cheers
CheenuSing
Hi Anonymous ,
Not very clear about the logic you have built for your measure Activity Listing. Does replacing MAX with MIN in above measure meet your requirement?
FILTER ( ALL('Listing History'[ExpiryDate]) ,[ExpiryDate] > MIN('Date Reported'[DateValue]) ) ,FILTER ( ALL('Listing History'[EffectiveEndDate]) ,[EffectiveEndDate] >= MIN('Date Reported'[DateValue]) )Would you please illustrate your scneario with some simplified dummy data? And show us desired result.
Regards,
Yuliana Gu
