Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
Building on PowerPivotPro's "Active Promotions pattern" I have tables of accounts which have:
I am looking to create a graph showing the number of accounts open each day. I have a date hierarchy on my date column. Putting this on my X axis with only "Month" selected seems to create the roughly correct figures but if I add "Date" and "Month" to the date hierarchy (so each data point represents a day) the result is too small, like it is only counting accounts which are open in the present day and were open then (in the past context).
Here is my current measure:
maxDate = MAX(DimDate[Date]) # Loans Open = VAR EndPeriod = [maxDate] Return CALCULATE( DISTINCTCOUNT(DimAccount[Pk]), FILTER( DimAccount, DimAccount[DateOpened] <= EndPeriod && OR(DimAccount[DateClosed] >= EndPeriod, DimAccount[status] = 1) && DimAccount[ProductTypeId] <> "D" ) )
Hi @pistachio ,
To understand your scenario better, could you share some data sample with table format and your desired output so that we could copy and test on it?
Best Regards,
Cherry
Hi, thanks for the reply. I've simplified the data and re-posted here:
thanks,
Tom
User | Count |
---|---|
124 | |
108 | |
99 | |
62 | |
62 |
User | Count |
---|---|
137 | |
116 | |
102 | |
71 | |
61 |