Calculate table and then column based on a date selection

[garzamalan]

So this is my issue.

I have a table that shows Entries / In Date / Out Date , to summarize.

I want to show to the user the number of items according to a aging category.

So my first step is to calculate aging ... Aging = Datediff([In Date], [Out Date], Day), and then I created another column with the name Category which has Switch([Aging], <10, " 0 - 10 days", < 20 , "10 - 20 days" ......) and so on.

Then I create a visual with the category calculated in the axis and a count on the values.

So far so good,

But my issue starts when I want to give the user the freedom to choose the date (out date). So insted of calculating the diff between 2 static dates, the end date will depend on the date the user selects.

So my tbale is like this.

Id	Customer	In Date	Out Date	Selected Date	Aging
E21146492	1	7/21/2021	9/22/2021
E21144643	2	7/20/2021	7/21/2021
E21146142	3	7/20/2021	8/22/2021
E21145922	4	7/19/2021	9/15/2021
E21145750	1	7/19/2021	7/30/2021
E21146042	2	7/19/2021	8/14/2021
E21145835	3	7/18/2021	9/2/2021
E21145834	4	7/18/2021	7/25/2021
E21145830	1	7/17/2021	8/9/2021
E21145828	2	7/17/2021	7/23/2021
E21145754	3	7/16/2021	7/20/2021
E21145153	4	7/16/2021	7/19/2021
E21145131	1	7/16/2021	7/19/2021
E21145619	2	7/16/2021	7/19/2021
E21144610	3	7/15/2021	7/16/2021
E21144320	4	7/15/2021	7/16/2021

I believe the category must be calculated with a column so I can then use it in the axis, is there a way to achieve this?

I do have a date table NOT related to this one, so I guess I can use that one somehow.

the whole reasoning is that I want to know the inventory aging, so If the user selects 7/31/2021, then the final visual should not show anything that got in after that date.

[v-yetao1-msft · Accepted solution]

Hi @garzamalan 

Please correct me if I wrongly understood your issue .

You want users can select [out date] freedomly and use the date they choose as a deadline .If they choose the date “ 7/31/2021” , the value of the date after “ 7/31/2021” will not display in the visual , right ?If in this case , I create a sample , you can refer to it .

(1)Create a new table with the field from Main data table .We will get a new date table with the column [Out Date]

Date = SELECTCOLUMNS('Main data',"Date",'Main data'[Out Date])

(2)Create a slicer with field [Date] from Date table .

(3)Through creating a measure to return the date before the date selected in Date table .

Measure = CALCULATE(SELECTEDVALUE('Main data'[Out Date]),FILTER('Main data','Main data'[Out Date]<=MAX('Date'[Date])))

(4)Put the measure in visual filter and set the measure is not blank .

And the final result is as shown :

I have attached my pbix file ,you can refer to it .

Best Regards

Community Support Team _ Ailsa Tao

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[lbendlin]

"But my issue starts when I want to give the user the freedom to choose the date (out date). So insted of calculating the diff between 2 static dates, the end date will depend on the date the user selects."

At that point you ca no longer use calculated columns. You need to switch to measures.

It is not clear what the user experience would look like though - would you expect the user selected out date to overrule the recorded out date?