datesinperiod but for start date use previous month as only need to report on complete/whole months

[akhaliq7]

I have the following datesinperiod code:

Table = 
 CALCULATETABLE(
     'Table',
     DATESINPERIOD(
         'Date'[Date],
         PREVIOUSMONTH(MAX('Date'[Date])),
         -12,
         MONTH
     )
)

But want the start date to be the previous month as only reporting on complete/whole months. e.g. today is 01/06/2023 so need my max date to be 31st of may 2023 and need the date to start 1st of june 2022

[tamerj1]

Hi @akhaliq7 

please try

Table =
CALCULATETABLE (
'Table',
FILTER (
DATESINPERIOD (
'Date'[Date],
PREVIOUSMONTH ( MAX ( 'Date'[Date] ) ),
-12,
MONTH
),
FORMAT ( 'Date'[Date], "YYMM" ) < FORMAT ( TODAY (), "YYMM" )
)
)

[akhaliq7 · Accepted solution]

I managed to solve my issue using the following code:

Table = 
CALCULATETABLE(
    'Table',
    DATESINPERIOD(
        'Date'[Date],
        EOMONTH(MAX('Date'[Date]), -1),
        -13,
        MONTH
    )
)

[kgoddard]

Based on your explanation, you need to make it -12 instead of -13... Using -13 would make the range: May 1, 2022 - May 31 2023; using -12 would make it  June 1, 2022 - May 31, 2023.

[akhaliq7]

Just to add when I created my date table I have set max date to today()

[akhaliq7]

I am getting the following error message: The first argument to 'PREVIOUSMONTH' must specify a column.