Dynamic Date field interacting with a table of data (with specific dates)

[JGARROD]

Hi I have the following data (sample) actual data includes over 9000 rows:

Entity	Bought	Incorporated	Retired	Sold
Liquid XR	12/05/2000	13/5/2020	17/02/2021	19/02/2021
Gerhold IR	12/05/2012
Toopy FR	30/09/2003	24/12/2019	25/10/2018
Dinky CK	12/05/2000
Upward FG	18/06/2005			20/01/2020
Justhol DR	12/05/2000		24/01/2019

I need my user to be able to select a date in time (any date between 1990 and today) and for powerBI to return the count of status of the assets at that time. 

e.g. date chosen : 01/01/2005 - 4 bought / 0 Incorporated/ 0 retired / 0 sold

       date chosen: 10/05/2020 - 3 bought / 2 retired / 1 sold

It basically needs to read the date that is closes and has passed and then pull through the status at that time. 

Id like this to be shown on something simple like a bar chart but again id like this to update when a date is selected via a selection box. 

Is this possible and if so how?

TIA

[JGARROD]

thanks @johnt75, hwoever this doesnt seem to be working. 

I am assuming I should have a date table set up however i am a bit lost as to how all these things interact. - after adding this measure to a visual i am just getting a blank visual. 

How should i be connecting the date table to this (if at all?) 

thanks 

[johnt75]

Yes, you would need a proper date table set up but it does not need to be related to your data table. Can you share a PBIX ?

[JGARROD]

Sure, i think ive got it working (almost) - im just getting stuck on how to link the date table in to have the change over time across teh X axis. 

thanks

- https://www.dropbox.com/s/du23echic96imk5/PowerBI%20Demo%20File.pbix?dl=0

Let me know how you get on - 

[johnt75]

Using the Day Month Year column is returning too many values, try using the Month Year column instead. You may also want to consider limiting the date range to further reduce the number of data points.

Finally, check out the SQL BI guys' take on how to get a custom date hierarchy to behave like an inbuilt one. https://www.sqlbi.com/articles/creating-a-simpler-and-chart-friendly-date-table-in-power-bi/ 

[JGARROD]

This is almost there, i am running into an issue where PowerBI seems to be counting null values for the status's - i.e in the first few years there is just a date against some for a 'merged' status however it seem to be counting values for all the other status's aswell.

As you can see there is no date in the columns for here on these specific rows but its counting them still, is there a way to remove this? prehaps a 'countifvalue'?

Link to current report - https://www.dropbox.com/s/aeyi9ue3pl4rr7k/PowerBI%20Demo%20File.pbix?dl=0

[johnt75]

The report file you attached doesn't show that, the dates start in 2006

[JGARROD]

I have just chenged the date range to show the above image, yet still in 2006 the chart doesnt display the correct results 😕 

[johnt75]

In each of the measures where you are checking that the primary date for that measure is <= maxDate you need to add a check to make sure that the primary date is not blank.

Also, check the logic on the measures where you are looking at other dates. In the Merger measure you are excluding cases where the Incorporated date is before the merger date, I don't know if that's what you want.

[JGARROD]

Ok, i iwll have a look thank you. There is not really a eirachy/rules for which dates come before others, in essence i needed powerBI to look across the row per item and check to see:

@ 'Date selected' what is the status of the closes date that has just passed....

but there is no order to which status it could be. i.e. if it is currenlty 'acquired' then it may turn to 'liquidated' or 'merged', likewise if its incorporated it may go stright to merged etc.

[johnt75]

Yes, you can add them all to the same visual, e.g. clustered column chart.

You could use a date slicer to pinpoint a data, you could also put the date on the x-axis of a chart and show changes in numbers over time.

[johnt75]

Create separate measures for each status like 

Bought Measure =
var maxDate = MAX('Date'[Date])
return CALCULATE( COUNTROWS('Table'),
'Table'[Bought] <= maxDate &&
( ISBLANK( 'Table'[Incorporated]) || 'Table'[Incorporated] > maxDate ) &&
( ISBLANK( 'Table'[Retired]) || 'Table'[Retired] > maxDate ) &&
( ISBLANK( 'Table'[Sold]) || 'Table'[Sold] > maxDate )
)

[JGARROD]

Thank you for your quick reply, if i were to create one of these for each status, would i then be able to add them all to the same visual?

Would i then use a date slice to pinpoint a date in time in order to show to desired outcome?