Cumulative Distinct Count For selected Dates

[Anonymous]

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.

[v-yulgu-msft]

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

[Anonymous · Accepted solution]

This measure was built to consider aggregate only when expirydate is null. But source data didn’t have anything null, after Investigation I found out ETL was wrong. Now this measure is working as expected. Thanks for the help

[CheenuSing]

Hi @Anonymous 

Can you please put some sample data / pbix file in googledrive or OneDrive and paste the link here, to figure a solution.

Cheers

CheenuSing

[Anonymous]

please Download pbix file here Listings.pbix

[CheenuSing]

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

[Anonymous]

We have to use all the filters which is given in measure "Active Listing" as this has filter for expired listings. If you add same filters I get same reults as Active listings. 

[CheenuSing]

Hi  @Anonymous 

Can you state in simple English the logic you want to filter before arriving at the final result.  That will be of great help.

Cheers

CheenuSing