Last 3 months (excluding the given month) calculations table for each month

[_smolar]

I'm having trouble creating the table with the number of records from last 3 months (excluding the given month).

I have a table "Query" with the records ID and with the dates they were created/opened. Every ID is unique.
Query

Record ID	Date Opened
2138216	14/01/2022
2183357	28/01/2022
2203134	01/02/2022
2206477	02/02/2022
2208422	15/02/2022
2213684	17/02/2022
2220932	03/03/2022
2222166	07/03/2022
2218023	19/03/2022
2228640	21/04/2022
2236210	29/04/2022

I need to create a table that in a given month will show the number of records from the last 3 months (counting from the month before so we exclude given month). What I am expecting are results like:

Date	Number of records
Jan 2022	0
Feb 2022	2
Mar 2022	6
Apr 2022	9
May 2022	11

I would like the data to be updated based on the entered "Date Opened", i.e. if new months appear when the table is refreshed, they will be added to the table.

So far, the only thing I have managed to create is the formula to calculate the number of records from the last 3 months. I built the Date Dimension table based on the "Date Opened" from the "Query". This is the measure:

last3months =

VAR EndDate =

EOMONTH('DateDimension'[MonthYear],-1)       ----- end of the previous month as an end date for calculation

VAR StartDate =

EOMONTH('DateDimension'[MonthYear],-4)+1   ---- end of the month 4 months earlier + 1 day to create the start day for calculation

VAR Result =

CALCULATE(

COUNTA('Query'[Record ID]),                              

DATESBETWEEN('Query'[Date Opened], StartDate, EndDate)   ----- count number of records between these dates

)

RETURN

Result

When I'm trying to create the table, the results are like below and I'm stuck at this point because I would like to have a division into months, not days.

Date Opened	Number of records
14/01/2022	0
28/01/2022	0
01/02/2022	2
02/02/2022	2
15/02/2022	2
17/02/2022	2
03/03/2022	6
07/03/2022	6
19/03/2022	6
21/04/2022	9
29/04/2022	9

Many thanks! 🙂 

[Anonymous]

Hi @_smolar ,

Please try this measure:

Number of Records = 
VAR _maxdate =
    EDATE ( MAX ( 'Calendar'[Date] ), -1 )
VAR _mindate =
    EDATE ( MAX ( 'Calendar'[Date] ), -4 ) + 1
VAR _num =
    CALCULATE (
        COUNT ( 'Table'[Record ID] ),
        FILTER (
            ALL('Table'),
            'Table'[Date Opened] >= _mindate
                && 'Table'[Date Opened] <= _maxdate
        )
    ) + 0
RETURN
    _num

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

[_smolar]

Hello @v-cgao-msft ,

Thank you for the quick reply. I tried your way but I have 2 problems:

1. the dates are not chronological. They are as in the pic below, so they are arranged alphabetically:

I  tried to create the table with the 'Date' as a value, not 'Month Year' and it looks better if it comes to chronological order, however I would like to have a 'Month Year' in the table. 

2. The second problem is that the number of records in the newly created table doesn't match the actual value. I wanted to check if it works correctly so I built the table with the number of records we had in particular months. It is simple table with the Date Opened and Record ID values. So for example value for the February 2021 should be 3206 because we had 3206 in January 2021. No. of record for the March 2021 should be 6936 (3730+3206) etc.

Let me know if you need more information regarding these errors. 

Kind Regards,

Katarzyna