topic Re: datesinperiod but for start date use previous month as only need to report on complete/whole months in DAX Commands and Tips

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

akhaliq7 — Thu, 01 Jun 2023 16:21:47 GMT

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

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

tamerj1 — Thu, 01 Jun 2023 20:09:14 GMT

Hi akhaliq7

please try

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

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

akhaliq7 — Fri, 02 Jun 2023 08:55:22 GMT

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

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

akhaliq7 — Fri, 02 Jun 2023 09:09:55 GMT

I managed to solve my issue using the following code:

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

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

akhaliq7 — Fri, 02 Jun 2023 09:11:32 GMT

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

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

Anonymous — Tue, 27 Jun 2023 15:15:11 GMT

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.