Filter open and old data and show in a table visual

[Anonymous]

Hi,

I have a series of orders, and I want to build a measure to get those entries that satisfy the following conditions:

1. Are older than 30 days (i.e. they are before the 1st day of the previous month)
2. Status is open

I have Data table and Date table which are related together. Example of data are:

Date	ID	Status
13/11/2021	1	Open
01/11/2021	2	Close
23/10/2021	3	Open
12/10/2021	4	Open
02/10/2021	5	Close
20/09/2021	6	Open
19/09/2021	7	Close
10/09/2021	8	Close

When using date filter:

If a date in December is selected:

1st day of previous month is 1 Nov 2021, thus open ones in Oct and Sep should be returned: rows 3, 4, 6

If a date in November is selected:

1st day of previous month is 1 Oct 2021, thus open ones in Sep should be returned: row 6

Note: I need a measure so that I can use it to filter a table visual which shows those open and old entries.

I have used this, but not working:

Open Orders =

VAR First_Day_Previous_Month = EOMONTH(LASTDATE('Date'[Date]),-2)+1

VAR Countt = CALCULATE(DISTINCTCOUNT('Data'[ID]))

RETURN

CALCULATE([Countt], FILTER('Data', 'Data'[Date] < First_Day_Previous_Month))

Please help me doing this task.

Thanks

[v-stephen-msft]

Hi @Anonymous ,

Here's my solution.

1.Create a calendar table.

Calendar = ADDCOLUMNS(CALENDAR(DATE(2020,1,1),DATE(2021,12,21)),"Year",YEAR([Date]),"Month",MONTH([Date]))

2.Create a measure and put it into filters. Set show items when the value is 1.

Measure = IF(ISFILTERED('Calendar'[Month]),IF(MAX('Table'[Date])<DATE(SELECTEDVALUE('Calendar'[Year]),SELECTEDVALUE('Calendar'[Month])-1,1)&&MAX('Table'[Status])="Open",1),1)

When you select December 2021 in the slicer, the result is as follows.

When November 2021 is selected.

Best Regards,

Stephen Tao

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

[Anonymous]

Thank you very much for your creative solution.

I actually have a big model that date table is related to data table. So, they are not separated.

In this condition, this equation seems to be not working.

In addition, do you believe this solution works if the date is accross two years? Does it work if we choose a data in January or February?

More data to test:

Date	ID	Status
2022/02/15	1	Open
2022/02/10	2	Close
2022/01/19	3	Open
2022/01/8	4	Close
2021/12/25	5	Open
2021/12/02	6	Close
2021/11/13	7	Open
2021/11/1	8	Close
2021/10/23	9	Open
2021/10/12	10	Open
2021/10/2	11	Close
2021/9/20	12	Open
2021/9/19	13	Close
2021/9/10	14	Close

[v-stephen-msft]

Hi @Anonymous ,

I modified the measure, and the slicer puts the date column of the calendar table.

Measure = IF(MAX('Table'[Date])<=EOMONTH(SELECTEDVALUE('Calendar'[Date]),-2)&&MAX('Table'[Status])="Open",1)

Best Regards,

Stephen Tao

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

[Anonymous]

Thanks so much for your help.

I have related tables, and the solution seems to be not working with it.

Regards

[Anonymous]

I don't understand why this measure doesn't work:

measure = CALCULATE(DISTINCTCOUNT('Table'[ID]), DATESBETWEEN('Date'[Date], FIRSTDATE('Date'[Date]), ENDOFMONTH(DATEADD(LASTDATE('Date'[Date]), -2, MONTH))), FILTER('Table', MAX('Table'[Status])="Open"))

Then using it as a filter and say measure > 0             😞

Any solution please?

[Jihwan_Kim]

Hi,

I suggest having a disconnected calendar table like below.

Please check the below picture and the attached pbix file.

Status measure: =
VAR selectedmonthnumber =
MAX ( 'Calendar'[Month number] )
VAR previousmonthnumber = selectedmonthnumber - 1
VAR datesincondition =
SUMMARIZE (
FILTER ( ALL ( 'Calendar' ), 'Calendar'[Month number] < previousmonthnumber ),
'Calendar'[Date]
)
RETURN
IF (
HASONEVALUE ( Data[Date] ),
MAXX (
FILTER ( data, Data[Date] IN datesincondition && Data[Status] = "Open" ),
Data[Status]
)
)

[Anonymous]

Thank you so much.

I actually cannot break the relations.

They are all part of a huge dataset and model.

If you have any other solutions, please let me know.

That would be greatly appreciated. 

[ryan_mayu]

@Anonymous 

pls see the attachment below. Hope it's helpful.

[Anonymous]

Thanks.

It seems that the solution does not fully work.

There are some issues:

1. If date is across two years (for example selected date is in January or February, this does not work.
2. The two tables are not related. I have a big model in which the date and data tables are related on date.

Kindly let me know if you have any other solution.

---------------------

As a test, I'm trying to add another column which shows recent/old events per row. But it is NOT working correctly (Maybe because of having a relation). 

Old - Recent =

VAR First_Day_Previous_Month = EOMONTH(LASTDATE('Date'[Date]),-2)+1

RETURN

IF(MAX('Table'[Date]) < First_Day_Previous_Month, "old", "recent")