cancel
Showing results for
Did you mean:
Highlighted
Regular Visitor

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

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)

Thanks
Fergal

1 ACCEPTED SOLUTION

Accepted Solutions
Moderator

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

@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,

2 REPLIES 2
Moderator

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

@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,

Regular Visitor

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

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])
)
))

Announcements

#### Save the new date (and location)!

Our business applications community is growing—so we needed a different venue, resulting in a new date and location. See you there!

#### Difinity Conference

The largest Power BI, Power Platform, and Data conference in New Zealand

#### Power Platform 2019 release wave 2 plan

Features releasing from October 2019 through March 2020

Top Solution Authors
Top Kudoed Authors (Last 30 Days)