Forum Discussion
Calculate table and then column based on a date selection
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.
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.
2 Replies
- lbendlin
Super User
"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?
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.
