Forum Discussion
Plot unique values during a time lapse
Sample data would help tremendously. Please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490
That being said, you should be able to use VAR to create a temp table of VALUES for an appropriately FILTER'ed table. You could then use EXCEPT to filter out previous values that you do not want to count repeatedly.
Another thing that might help is my Time Intelligence The Hard Way measure here:
https://community.powerbi.com/t5/Quick-Measures-Gallery/Time-Intelligence-quot-The-Hard-Way-quot-TITHW/m-p/434008
Community ChampionSample data would help tremendously. Please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490
That being said, you should be able to use VAR to create a temp table of VALUES for an appropriately FILTER'ed table. You could then use EXCEPT to filter out previous values that you do not want to count repeatedly.
Another thing that might help is my Time Intelligence The Hard Way measure here:
https://community.powerbi.com/t5/Quick-Measures-Gallery/Time-Intelligence-quot-The-Hard-Way-quot-TITHW/m-p/434008
Hello Sir,
Thank you for your assesment upon my issue related to my MX VRD dashboard. I understand that I didn't comply with some recommendations related to the communication protocol for problems. I will try to be wise on how to explain the approach now:
This is my dashboard. I track vehicles and the faults that happen during a fixed period of time (since 2016 - YTD).
Customers reference. Counts distinct Customers for the complete period of timeVIN reference, it counts distinct VIN
Data comes from the same excel file (MX VRD), hence I didn't have to create a relationship.
I use two cards that display the quantity of VIN, and quantity of CUSTOMERS using COUNT (distinct) function.
As I do NOT filter these cards what I get is the complete amount of VIN and CUSTOMERS during the whole period.
By trying to plot the development of when unique CUSTOMERS and unique VIN have happened for the first time, I used the count (distinct) function, but I notice that the bottomline number is greater than my original total VIN number. The same happened for CUSTOMER. I understand that during the period of time VIN and CUSTOMER happen more than just one time. VIN can happen in 2017 and 2018. Which is the way now it is plotted.
Total VIN = 338 times
Count (distinct) VIN by drilled by years = (2016) 10 + (2017) 175 + (2018) 282 + (2019) 68 = 535 times
I jumped into the quick measures reference to see if I could find something that helped to isolate the data for CUSTOMERS and VIN when they happen for the first time.
From quick measures I tried to use the Sales for New customers approach though, I believe this DAX is selecting NEW VIN with NEW CUSTOMERS, hence the result that I get is lower from what I need.
Count of Vin for new Customer Company Name =
IF(
ISFILTERED('MX VRD'[Created At]),
ERROR("Time intelligence quick measures can only be grouped or filtered by the Power BI-provided date hierarchy or primary date column."),
VAR __CURRENT_VALUES = SUMMARIZE(VALUES('MX VRD'), 'MX VRD'[Customer Company Name])
VAR __EXISTING_VALUES =
CALCULATETABLE(
SUMMARIZE(VALUES('MX VRD'), 'MX VRD'[Customer Company Name]),
FILTER( ALL('MX VRD'[Created At].[Date]), 'MX VRD'[Created At].[Date] < MIN('MX VRD'[Created At].[Date]) ),
KEEPFILTERS(__CURRENT_VALUES) )
RETURN
CALCULATE(
DISTINCTCOUNT('MX VRD'[Vin]),
EXCEPT(__CURRENT_VALUES, __EXISTING_VALUES)
)
)
Total VIN = 338 times
Count (distinct) VIN by drilled by years by NEW CUSTOMERS = (2016) 10 + (2017) 138 + (2018) 51 + (2019) 5 = 204 times
By sharing my PBIX would be easier to make me explain to you Sir?
Please let me know
Thank you.