Status Change

[dbattin4]

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(Opportunities_ME[OpportunityNumber]) // Get the current item being evaluated

VAR LastDateInMonth = [Last_ME] // Find the last day of the previous month

RETURN

    CALCULATE (

        LASTNONBLANKVALUE(Opportunities_ME[OpportunityNumber], Min(Opportunities_ME[StateCode])), // Returns the last state value encountered

        FILTER (

            ALL(Opportunities_ME), // Removes all filters from the table

            Opportunities_ME[OpportunityNumber] = CurrentItem && // Keeps only the context of the current item

            Opportunities_ME[IngestionDate_EOM]= LastDateInMonth

        )

    )

Not sure what I have got wrong.
IngestionDate_EOM holds the month end dates and I am trying to see what the StateCode was last month for each OppurtunityNumber

[v-agajavelly · Accepted solution]

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.

[dbattin4]

@v-agajavelly this worked perfectly thank you. Thank you to everyone else as well @OktayPamuk80 @srlabhe @danextian 

[OktayPamuk80]

Hi,

It is recommended to always have a good basis (in this case your data model), which enables you creating desired measures and visualisations. I don't think, it is possible without an additional calendar table (or not functionally good solution)

[srlabhe]

Try using below

PreviousMonthState =
VAR CurrentOpportunity = SELECTEDVALUE(Opportunities_ME[OpportunityNumber])
VAR MaxIngestionDate = CALCULATE(MAX(Opportunities_ME[Ingestion Date_EOM]), ALL(Opportunities_ME))
VAR PreviousMonthEnd = EOMONTH(MaxIngestionDate, -1)
RETURN
CALCULATE(
LASTNONBLANKVALUE(
Opportunities_ME[Ingestion Date_EOM],
MIN(Opportunities_ME[StateCode])
),
ALLEXCEPT(Opportunities_ME, Opportunities_ME[OpportunityNumber]),
Opportunities_ME[OpportunityNumber] = CurrentOpportunity,
Opportunities_ME[Ingestion Date_EOM] = PreviousMonthEnd
)

[danextian]

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.

[OktayPamuk80]

Hi,

I think there is a step you need to do before you can achieve your need. Instead of using the date field from your Opportunities_ME table (and year and month for example), you need to create a separate calendar and connect the 2 tables over the date field. By this, you can select one month see data and still can have different measures showing you previous month or year.

You can create a calendar in the table view in Power BI Desktop using the "New Table":

You then type:

Calendar = calendar (min(Opportunities_ME[Date]),max(Opportunities_ME[Date]))

You get a table called Calendar including a Date field from a range Date in your Opportunities_ME table. You can create additional year, Yearmonth, month, etc. fields to be able to filter or use it in charts.

Once you connect the two tables in the model view, you can create the new measure:

PreviousMonthState = calculate(Min(Opportunities_ME[StateCode]), previousmonth(Calendar[Date]))

Regards,

Oktay

If it helped you would appreciate for a KUDOS and mark the answer as solution.

[dbattin4]

I have a semantic model where I am unable to add a seperate calendar table. There is a global calendar available but can you do this without using the calendar? If not I will get a relationship created