Select 2 Dates in Filters on Main dashboard & see projects (in table) which have changes status/valu

[AdithyaVar]

Data Structure

Project	Name	Staus	Start Date	Amount	Reporting Date
1	abc	Open	7/1/2021	1000	11/1/2021
2	def	Close	7/5/2021	2000	11/1/2021
3	ghi	Cancelled	6/2/2021	3000	11/1/2021
4	jkl	Inactive	8/4/2021	4000	11/1/2021
5	mno	Inactive	6/1/2021	5000	11/1/2021
6	pqr	Open	7/18/2021	6000	11/1/2021
7	stu	Hold	7/7/2021	7000	11/1/2021
1	abc	Hold	7/19/2021	1000	11/2/2021
2	def	Close	7/5/2021	2500	11/2/2021
3	ghi	Cancelled	6/20/2021	3000	11/2/2021
4	jkl	Active	8/4/2021	3300	11/2/2021
5	mno	Inactive	6/1/2021	5000	11/2/2021
6	pqr		7/7/2021	6200	11/2/2021
7	stu	Hold	7/7/2021	7000	11/2/2021
1	abc	Open	7/1/2021	1200	11/3/2021
2	def	Close	7/5/2021	2500	11/3/2021
3	ghi	Cancelled	6/2/2021	0	11/3/2021
4	jkl	Active	8/4/2021	3300	11/3/2021
5	mno	Active	6/1/2021	5250	11/3/2021
6	pqr	Open	7/18/2021		11/3/2021
7	stu	Hold	7/7/2021	7000	11/3/2021
8	vwx	Open	8/12/2021	9000	11/3/2021
1	abc	Open	7/1/2021	1200	11/4/2021
2	def	Close	7/5/2021	2500	11/4/2021
3	ghi	Cancelled	6/2/2021	4000	11/4/2021
4	jkl	Inactive	8/4/2021	3300	11/4/2021
5	mno	Inactive	6/1/2021	5250	11/4/2021
6	pqr	Open	7/18/2021	6200	11/4/2021
7	stu	Hold	7/7/2021	7400	11/4/2021
1	abc	Open	7/1/2021	1200	11/5/2021
2	def	Close	7/5/2021	2500	11/5/2021
3	ghi	Cancelled	6/2/2021	4000	11/5/2021
4	jkl	Inactive		3300	11/5/2021
5	mno	Inactive	6/1/2021	5250	11/5/2021
6	pqr	Open		6200	11/5/2021
7	stu	Hold	7/10/2021	7400	11/5/2021
8	vwx	Open	8/12/2021	9000	11/5/2021

Output & use cases are mentioned below

[v-yangliu-msft · Accepted solution]

Hi  @AdithyaVar ,

Here are the steps you can follow：

1. Create calculated table.

Date = DISTINCT('Table'[Reporting Date])

2. Create measure.

Start Date 1  &&  Start Date 2:

Start Date -1 =
var _min=MIN('Date'[Date])
var _max=MAX('Date'[Date])
return
CALCULATE(MAX('Table'[Start Date]),FILTER(ALL('Table'),'Table'[Project]=MAX('Table'[Project])&&'Table'[Reporting Date]=_min))

Start Date -2 =
var _min=MIN('Date'[Date])
var _max=MAX('Date'[Date])
return
CALCULATE(MAX('Table'[Start Date]),FILTER(ALL('Table'),'Table'[Project]=MAX('Table'[Project])&&'Table'[Reporting Date]=_max))

Flag =
IF(
[Start Date -1]=[Start Date -2],0,1)

Status1  &&  Status2:

Status1 =
var _min=MIN('Date'[Date])
var _max=MAX('Date'[Date])
return
CALCULATE(MAX('Table'[Staus]),FILTER(ALL('Table'),'Table'[Project]=MAX('Table'[Project])&&'Table'[Reporting Date]=_min))

Status2 =
var _min=MIN('Date'[Date])
var _max=MAX('Date'[Date])
return
CALCULATE(MAX('Table'[Staus]),FILTER(ALL('Table'),'Table'[Project]=MAX('Table'[Project])&&'Table'[Reporting Date]=_max))

Flag2 =
IF(
[Status1]=[Status2],0,1)

Value1  &&  Value2:

Value1 =
var _min=MIN('Date'[Date])
var _max=MAX('Date'[Date])
return
CALCULATE(MAX('Table'[Amount]),FILTER(ALL('Table'),'Table'[Project]=MAX('Table'[Project])&&'Table'[Reporting Date]=_min))

Value2 =
var _min=MIN('Date'[Date])
var _max=MAX('Date'[Date])
return
CALCULATE(MAX('Table'[Amount]),FILTER(ALL('Table'),'Table'[Project]=MAX('Table'[Project])&&'Table'[Reporting Date]=_max))

Flag3 =
IF(
    [Value1]=[Value2],0,1)

3. Put [Flag], [Flag2], [Flag3] in each Visual Filter, and set is=1, apply filter.

4. Result:

Use Date table [Date] as slicer

Start Date 1  &&  Start Date 2:

Status1  &&  Status2:

Value1  &&  Value2:

Best Regards,

Liu Yang

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

[AdithyaVar]

Hi Amit, i was able to do perform few steps to get partial requirement

Since the above is to represent value it is fine. But i want to compare what has been the status for these projects between 2 dates, which are 'String'. in that case how can we do it

[AdithyaVar]

@amitchandak , 

@Greg_Deckler ,

@parry2k 

Any help on this would be great

[amitchandak]

@AdithyaVar , If they are select as range using a date table.

A measure

//Difference between two selected dates, Assume range as been selected. A date table
diff =
var _max = maxx(allselected('Date'),'Date'[Date])
var _min = maxx(allselected('Date'),'Date'[Date])
return
if( calculate( Max(Table[Status]), filter('Date', 'Date'[Date]=_min )) <>
calculate( Max(Table[Status]), filter('Date', 'Date'[Date] =_max )) , "Changed", "Same")

Or refer if you need two period

How to use two Date/Period slicers

https://youtu.be/WSeZr_-MiTg