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(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
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.

7 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    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.

  • OktayPamuk80's avatar
    OktayPamuk80
    Responsive Resident
    11 months ago

    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's avatar
      dbattin4
      Frequent Visitor
      11 months ago

      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

       

  • 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.

  • srlabhe's avatar
    srlabhe
    Super User
    11 months ago

    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
    )

  • OktayPamuk80's avatar
    OktayPamuk80
    Responsive Resident
    11 months ago

    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)