Forum Discussion

smather's avatar
smather
Icon for Helper III rankHelper III
3 years ago

Dataverse - Dynamics 365, Track Stage Changes

Hi there   Would there be a way (preferably in DAX tho if it needs to be done within Power Query, that's fine) to view what stage an oppotunity was in the last 24 hours? EG:   A job was at Propos...
Need Help
Anonymous's avatar
Anonymous
Not applicable
3 years ago

Hi smather ,

Please try this measure:

Measure = 
VAR _MAX_DATETIME = 
CALCULATE(
    MAX('Table'[DateTime]),
        FILTER(
            'Table',
            'Table'[Job ID]=MAX('Table'[Job ID])
                &&'Table'[DateTime]>=NOW()-1
                &&'Table'[DateTime]<=NOW()
        )
)
VAR _LAST_STAGE = 
CALCULATE(
    MAX('Table'[Stage]),
        'Table'[Job ID]=MAX('Table'[Job ID])
            &&'Table'[DateTime]<_MAX_DATETIME
)
RETURN
    _LAST_STAGE

Result:

Best Regards,
Gao
Community Support Team

 

If there is any post helps, then please consider Accept it as the solution  to help the other members find it more quickly. If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

How to get your questions answered quickly -- How to provide sample data

smather's avatar
smather
Icon for Helper III rankHelper III
3 years ago

Hi Anonymous 

 

I tried out your measure, and it doesn't pull back any information, even though I have several jobs that have been modified today/over the weekend. Any other suggestions? 😞 

 

I tried it both as a measure and as a col in Data

    • smather's avatar
      smather
      Icon for Helper III rankHelper III
      3 years ago

      Hi Anonymous 

       

      My data kinda looks like the below: 

       

      Modified OnProject RefActive StageState Code NameStatus CodeEst Revenue
      04/05/2022 12:52:07INV001Project CompleteWonWon£1,000.00
      12/10/2022 15:48:32INV256Sales CallOpenIn Progress£2,546,000
      02/11/2022 12:01:06INV478Kick OffWonWon£500
      12/10/2022 08:35:59INV455Project CompleteWonWon£8,975.32
      14/04/2022 16:00:00INV123Needs AnalystOpenIn Progress£698.26

       

      followed by a lot more data. What I'm looking to do, is to have more than one thing tracked -- the active stage, State Code and Status Code, so if the last 24hrs if they change, for it say what it was previously 24hrs ago. I'd like to do something similar with the Est Revenue col, so if that value has increased/decreased in the last 24hrs also. 

       

      Any help would be appreciated and hopefully that's enough of an idea of how my dataset looks like 🙂 

      • Anonymous's avatar
        Anonymous
        Not applicable
        3 years ago

        Hi smather ,

        Take [Active Stage] as an example to check if this is the result you want.

        Active Stage2 = 
VAR _MAX_DATETIME = 
CALCULATE(
    MAX('Table'[Modified On]),
    FILTER(
        ALL('Table'),
        'Table'[Project Ref]=MAX('Table'[Project Ref])
    )
)
VAR _LAST_STAGE = 
CALCULATE(
    MAX('Table'[Active Stage]),
    FILTER(
        ALL('Table'),
        'Table'[Project Ref]=MAX('Table'[Project Ref])
            &&'Table'[Modified On]<_MAX_DATETIME
            &&'Table'[Modified On]>=_MAX_DATETIME-1
    )
)
VAR _RESULT = IF(MAX('Table'[Modified On])=_MAX_DATETIME,_LAST_STAGE)
RETURN
    _RESULT

        Best Regards,
        Gao
        Community Support Team

         

        If there is any post helps, then please consider Accept it as the solution  to help the other members find it more quickly. If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

        How to get your questions answered quickly -- How to provide sample data