Create new measure to compare values from two dates

[AFra]

Hi all, 

I have a table such as : 

Reporting date	ID Project	Status	# houses	RP date
31-12-20	Volt	MS_Prep	24	07-07-24
31-12-20	Miroi	MS_AvProj	137	25-06-23
31-12-20	PAS II	Chantier	100	24-03-25
31-12-20	Navez	En attente/bloqué	64	12-12-22
30-06-21	Volt	MS_Prep	18	07-07-24
30-06-21	Miroi	MS_AvProj	137	30-08-23
30-06-21	PAS II	Chantier	100	20-12-25
30-06-21	Navez	Chantier	75	12-12-22
30-06-21	Luttr	MS_Prep	100	10-05-25

I would like to create several measures would allow me to compare data from the latest date (here 30-06-2021, but il will change over the time) to the previous one (here 31-12-2020). I need to display : 

- List of new projects (in the example : Luttr)

- List of projects that changed status (here : Navez)

- List of projects that changed # houses (here : Volt & Navez)

- List of projects that changed RP date (here : Miroi & PAS II)

I guess I need to use an if statement but I can't figure how to specify "compare each project based on the date. Take in account only the latest date & the previous one". 

thanks in advance for your help! 

Ana 

[v-angzheng-msft · Accepted solution]

Hi, @AFra 

This took me some time, but I got the results.
Create the following measures:
the Date:

_LastDate =
VAR _top1 =
    TOPN ( 1, ALL ( 'Table' ), [Reporting date], DESC )
RETURN
    MAXX ( _top1, [Reporting date] )

_previousDate =
VAR _t =
    FILTER ( ALL ( 'Table' ), 'Table'[Reporting date] < [_LastDate] )
VAR _preProject =
    TOPN ( 1, _t, [Reporting date], DESC )
VAR _preDate =
    SUMMARIZE ( _preProject, [Reporting date] )
RETURN
    MAXX ( _preDate, [Reporting date] )
// MAXX(FILTER(ALL('Table'),'Table'[Reporting date]<MAX('Table'[Reporting date])),[Reporting date])

 NewProject:

_NewProject =
VAR _preProject =
    SUMMARIZE (
        FILTER ( ALL ( 'Table' ), 'Table'[Reporting date] = [_previousDate] ),
        'Table'[ID Project]
    )
VAR _if =
    IF (
        MAX ( 'Table'[Reporting date] ) = [_LastDate],
        IF ( MAX ( 'Table'[ID Project] ) IN _preProject, BLANK (), 1 )
    )
RETURN
    _if

 House, RP date, Status:  
just change the distinctount field

_ChangeStatus =
VAR _count =
    SUMMARIZE (
        FILTER ( ALL ( 'Table' ), 'Table'[Reporting date] >= [_previousDate] ),
        [ID Project],
        "Count", DISTINCTCOUNT ( 'Table'[Status ] )
    )
VAR _Change =
    FILTER ( _count, [Count] > 1 )
VAR _Project =
    SUMMARIZE ( _Change, [ID Project] )
VAR _if =
    IF (
        MAX ( 'Table'[Reporting date] ) = [_LastDate],
        IF ( MAX ( 'Table'[ID Project] ) IN _Project, 1, BLANK () )
    )
RETURN
    _if

 Result:

Please refer to the attachment below for details

Hope this helps.

Best Regards,
Community Support Team _ Zeon Zheng
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[AFra]

thank you! it works nicely!!!

[AFra]

Hello @amitchandak, 

thank you for your suggestion, but it doesnt seem to work : it does not retrieve the right lists. 

Here's the full data if you - or anyone else - are in the mood for help : ) link to data 

[amitchandak]

@AFra , Create measures like

project Not having same status =
var _max = maxx(allselected(Table),Table[RP date])
var _max = minx(filter(allselected(Table),Table[RP date] <_max),Table[RP date])

var _maxs = calculate(Max(Table[Status]), filter(Table, Table[RP date] = _max)) //use allselected(Table) , if needed
var _mins =calculate(Max(Table[Status]), filter(Table, Table[RP date] = _max)) //use allselected(Table) , if needed
return
countx(values(Table[Project]), if(_maxs<> _minx ,[Project], blank()))

New Project =
var _max = maxx(allselected(Table),Table[RP date])
var _max = minx(filter(allselected(Table),Table[RP date] <_max),Table[RP date])

var _maxs = calculate(count(Table[Status]), filter(Table, Table[RP date] = _max))//use allselected(Table) , if needed
var _mins =calculate(count(Table[Status]), filter(Table, Table[RP date] = _max))//use allselected(Table) , if needed
return
countx(values(Table[Project]), if(isblank(_mins) && not(isblank(_maxs)) ,[Project], blank()))

The last two need to have the same approach like formula one