Forum Discussion

dbattin4's avatar
dbattin4
Frequent Visitor
11 months ago
Solved

Status Change

I have the follwoing measure but it is not returning any data and I am trying it to return what the status was at the previous month end PreviousMonthState = VAR CurrentItem = SELECTEDVALUE(Op...
Need Help
  • Anonymous's avatar
    Anonymous
    11 months ago

    Hi dbattin4 ,

    Thanks danextian , srlabhe  and OktayPamuk80  for the detailed suggestions.

    The approaches were on point, but the missing piece was that my dataset doesn’t always have entries on the exact month-end date. That’s why my original measure returned blanks.
    As for my knowledge we  can solve it by tweaking the logic to grab the latest record on or before the previous month end instead of looking for that single date.
    Here’s the final measure that worked for me (sharing in case it helps others too).

    PreviousMonthState = 
    VAR _currentOpp = SELECTEDVALUE(Opportunities_ME[OpportunityNumber]) 
    VAR _currentDate = MAX(Opportunities_ME[IngestionDate_EOM]) 
    VAR _prevMonthEnd = EOMONTH(_currentDate, -1)
    VAR _lastAvailableDate = CALCULATE( MAX(Opportunities_ME[IngestionDate_EOM]), FILTER( ALL(Opportunities_ME), Opportunities_ME[OpportunityNumber] = _currentOpp && Opportunities_ME[IngestionDate_EOM] <= _prevMonthEnd ) )
    VAR _state = CALCULATE( MAX(Opportunities_ME[StateCode]), FILTER( ALL(Opportunities_ME), Opportunities_ME[OpportunityNumber] = _currentOpp && Opportunities_ME[IngestionDate_EOM] = _lastAvailableDate ) )
    RETURN _state


    Really appreciate all the guidance here.

    Thanks,
    Akhil.

danextian's avatar
danextian
Super User
11 months ago

hI dbattin4 

Try either of the following measures:

State PM = 
VAR _currentEOM =
    SELECTEDVALUE ( Data[IngestionDate_EOM] )
VAR _currentOpp =
    SELECTEDVALUE ( Data[OpportunityNumber] )
RETURN
    MINX (
        TOPN (
            1,
            FILTER (
                SUMMARIZE (
                    FILTER (
                        ALL ( Data ),
                        Data[IngestionDate_EOM] < _currentEOM
                            && Data[OpportunityNumber] = _currentOpp
                    ),
                    Data[OpportunityNumber],
                    Data[IngestionDate_EOM],
                    Data[StateCode]
                ),
                [IngestionDate_EOM] < _currentEOM
            ),
            [IngestionDate_EOM], DESC
        ),
        [StateCode]
    )

State PM2 = 
VAR OppStatus =
    INDEX (
        1,
        SUMMARIZE (
            FILTER (
                ALL ( 'Data' ),
                Data[IngestionDate_EOM] < SELECTEDVALUE ( Data[IngestionDate_EOM] )
            ),
            'Data'[OpportunityNumber],
            'Data'[StateCode],
            'Data'[IngestionDate_EOM]
        ),
        ORDERBY ( 'Data'[IngestionDate_EOM], DESC ),
        PARTITIONBY ( 'Data'[OpportunityNumber] )
    )
RETURN
    MAXX ( OppStatus, [StateCode] )

 

But a separate EOM table would have simplified the calculations

 

State PM3 = 
CALCULATE (
    SELECTEDVALUE ( Data[StateCode] ),
    PREVIOUSMONTH ( EOM[IngestionDate_EOM] ),
    REMOVEFILTERS ( EOM )
)

Please see the attached pbix.