Need to determine MIN/MAX dates (date ranges) for previous month

[FergalK]

Hi
Appreciate some help with this - I have a custom calendar, and I need a DAX formula to provide me with the MIN/MAX dates for the previous month (or time period).

For example, our company calendar month of May 2017 goes from 04/30/2017 to 05/27/2017, the month of June 2017 goes from 05/28/2017 to 07/01/2017.

Let's say I have a report which is looking at Sales for June 2017, I also want a column in the report showing Sales for May 2017, (and a column showing the % difference between them)

I need a DAX query which knows that the previous period/month to June, is May, and that the dates in May are from 04/30/2017 to 05/27/2017 (I have a CompanyDates table with the various dates and months in it)

Appreciate your help with this!

Thanks
Fergal

[v-sihou-msft · Accepted solution]

@FergalK

Since you are not in standard calendar, all build-in DAX time intelligence functions can't be used in this scenario. You should have numeric Fiscal Month and Fiscal Year columns along with standard calendar dates in your table. Then you can get the fiscal month range based on those two columns. The measure can be like:

Prev Fiscal Month Sales =
CALCULATE (
    SUM ( Table[Sales] ),
    FILTER (
        ALL ( 'CompanyDates' ),
        CompanyDates[Fiscal Month]
            = MAX ( CompanyDates[Fiscal Month] ) - 1
            && CompanyDates[Fiscal Year] = MAX ( CompanyDates[Fiscal Year] )
    )
)

Regards,

[FergalK]

thanks a lot for that @v-sihou-msft

I modified your query to help account for the year-rollover per below:

IntelPrevMnth:=
CALCULATE (
SUM ('Rpr_data'[recs_qty]),
FILTER (
ALL ( 'Receipt dates' ),
'Receipt dates'[intel_month] =
IF (MAX('Receipt dates'[intel_month])=1,12,MAX('Receipt dates'[intel_month] ) -1) &&
'Receipt dates'[Receipt Year] =
IF (MAX('Receipt dates'[intel_month])=1,MAX('Receipt dates'[Receipt Year])-1, MAX('Receipt dates'[Receipt Year])
)
))