Retrospecive analysis based on Status History table

[IvanS]

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 status as it was by end of the previous months using the table Status History with following fields:

1. Deal ID

2. Deal Amount

3. Status

4. Modified date

Please find below example - for this particular deal, the measure will count the statuses as per below:

31st of May - deal was not created yet therefore it will not be counted into any status (this applies also for all months before May)

30th of June - based on checking last status in June, deal will be counted under status Agreement accepted

31st of July - based on checking last status in June, deal will be counted under status Agreement signed

31st of August - measure to check if there was recent change in August (for example Completed), if yes, then to count deal under newest status and if no, then to keep counting the deal under the Agreement signed status

Thank you for any help

Ivan

[Anonymous]

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]

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 month	Status	Nr. of Deals
June 2023	Agreement accepted	1
July 2023	Agreement signed	1
August 2023	Agreement signed	1
September 2023	Agreement signed	1
October 2023	Agreement signed	1
November 2023	Agreement signed	1
December 2023	Agreement signed	1

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