Forum Discussion

IvanS's avatar
IvanS
Icon for Helper V rankHelper V
3 years ago

Retrospecive analysis based on Status History table

Hello guys,   I am working on one request where I need to provide retrospecitve analysis of statuses by the end of each month (previous, pre-previous etc.).   Basically, I need to recalculate sta...
Anonymous's avatar
Anonymous
Not applicable
3 years ago

Hi  IvanS ,

 

Here are the steps you can follow：

1. Create calculated column.

Column =
SWITCH(
    TRUE(),
    MONTH('Table'[LastModifiedTime])=6&&    'Table'[LastModifiedTime]=MAXX(FILTER(ALL('Table'),'Table'[Dealld]=EARLIER('Table'[Dealld])&&YEAR('Table'[LastModifiedTime])=YEAR(EARLIER('Table'[LastModifiedTime]))&&MONTH('Table'[LastModifiedTime])=MONTH(EARLIER('Table'[LastModifiedTime]))),[LastModifiedTime]),"Agreement accepted",
    MONTH('Table'[LastModifiedTime])=7&&  'Table'[LastModifiedTime]=MAXX(FILTER(ALL('Table'),'Table'[Dealld]=EARLIER('Table'[Dealld])&&YEAR('Table'[LastModifiedTime])=YEAR(EARLIER('Table'[LastModifiedTime]))&&MONTH('Table'[LastModifiedTime])=MONTH(EARLIER('Table'[LastModifiedTime]))),[LastModifiedTime]),"Agreement signed",
    MONTH('Table'[LastModifiedTime])=8&&   'Table'[LastModifiedTime]=MAXX(FILTER(ALL('Table'),'Table'[Dealld]=EARLIER('Table'[Dealld])&&YEAR('Table'[LastModifiedTime])=YEAR(EARLIER('Table'[LastModifiedTime]))&&MONTH('Table'[LastModifiedTime])=MONTH(EARLIER('Table'[LastModifiedTime]))),[LastModifiedTime]),"newest status","Data collecting")

2. Result:

 

You can see if it meets your expected results, if not, you can show the expected results in the form of pictures, we can help you better.

 

Best Regards,

Liu Yang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly

  • IvanS's avatar
    IvanS
    Icon for Helper V rankHelper V
    3 years ago

    Hi Anonymous ,

     

    seems that we misunderstood. I see in your DAX function the month order which is hard-coded value. But I need to have this dynamic. The result is pretty easy to describe. 

     

    Based on table that is tracking status history, I need to retrospetively calculate the number of deals per it's status by end of each month.

     

    So in case we have only 1 deal as per my previous screenshot (and there would be no other deal), the result will look like below:

     

     

    Date (from Date table) - end of monthStatusNr. of Deals
    June 2023Agreement accepted1
    July 2023Agreement signed1
    August 2023Agreement signed1
    September 2023Agreement signed1
    October 2023Agreement signed1
    November 2023Agreement signed1
    December 2023Agreement signed1

     

    Having more deals and status changes, there will be more lines for each month where we had at least 1 deal in the history.

     

    Hope it is more clear now.

     

    Ivan