Forum Discussion

AFra's avatar
AFra
Icon for Helper III rankHelper III
5 years ago
Solved

Create new measure to compare values from two dates

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 ...
Need Help
  • v-angzheng-msft's avatar
    v-angzheng-msft
    5 years ago

    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.

amitchandak's avatar
amitchandak
Icon for Super User rankSuper User
5 years ago

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