How to forecast number of events that will age each month in the future?

[Hyuna_8000]

Hi,

I'm pretty new to PowerBI DAX and M code, it would be greatly appreciated if anyone can help me create a calculated table to forecast number of events age 1 year in every future month.

Here is the scenario:
I have a list of events that are created on different date (Date Logged), and I would like to know how many of them will age 1 year old every month in the future, below is a sample of the data:

Input

Event Number	Date Logged
RET001	05/01/2021
RET002	01/02/2021
RET003	10/03/2021
RET004	30/04/2021
RET005	31/05/2021
RET006	01/06/2021
RET007	18/06/2021
RET008	08/07/2021
RET009	26/08/2021
RET010	28/09/2021
RET011	08/10/2021
RET012	11/10/2021

When the event has reached 1 year old, we will mark it as an Aged event. and we would like to have a summary table that forecast how many events that would age at the end of each month in the future. I will take the above table as an example:

• As of now, there are already 3 Aged events
• By the end of April-2022 (30/04/2022), there will be 1 event ages to 1 year old (RET004),
• By the end of May-2022 (31/05/2022), there will be 1 event ages to 1 year old (RET005)
• By the end of June-2022 (30/06/2022), there will be 2 events age to 1 year old (RET006 and RET007)
• it goes on....

Based on the input, I would like to generate a summary table or something similar as follow:

Output:

Aged Month	No. of Event
Already aged	3
Age in 30/04/2022	1
Age in 31/05/2022	1
Age in 30/06/2022	2
Age in 31/07/2022	1
Age in 31/08/2022	1
Age in 30/09/2022	1
Age in 31/10/2022	2

I hope this summary table is achievable either via M code or DAX. I've been trying a lot of things(create new column for each month and do datediff..) but still haven't been able to figure out.

Much appreciated for any ideas, thanks so much.

[Jihwan_Kim · Accepted solution]

Hi,

I am not sure how your data model looks like, but please check the below picture and the attached pbix file.

I hope this can provide some ideas on how to create a new table as you desire.

New Table = 
ADDCOLUMNS (
    UNION (
        ROW ( "@AgedMonth", "AlreadyAged" ),
        FILTER (
            SUMMARIZE (
                ADDCOLUMNS (
                    CALENDAR (
                        DATE ( YEAR ( MIN ( Data[Date Logged] ) ) + 1, MONTH ( MIN ( Data[Date Logged] ) ), 1 ) - 1,
                        DATE ( YEAR ( MAX ( Data[Date Logged] ) ) + 1, MONTH ( MAX ( Data[Date Logged] ) ), 1 )
                    ),
                    "@AgedMonth", EOMONTH ( [Date], 0 )
                ),
                [@AgedMonth]
            ),
            [@AgedMonth] >= TODAY ()
        )
    ),
    "@NoOfEvent",
        IF (
            [@AgedMonth] = "AlreadyAged",
            COUNTROWS (
                FILTER (
                    Data,
                    Data[Date Logged]
                        < DATE ( YEAR ( TODAY () ) - 1, MONTH ( TODAY () ), 1 )
                )
            ),
            COUNTROWS (
                FILTER (
                    Data,
                    Data[Date Logged]
                        <= DATE ( YEAR ( [@AgedMonth] ) - 1, MONTH ( [@AgedMonth] ) + 1, 1 ) - 1
                        && Data[Date Logged]
                            >= DATE ( YEAR ( [@AgedMonth] ) - 1, MONTH ( [@AgedMonth] ), 1 )
                )
            )
        )
)

[Hyuna_8000]

Thanks @Jihwan_Kim , it works!