cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
FergalK Regular Visitor
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)

Appreciate your help with this!

Thanks
Fergal

1 ACCEPTED SOLUTION

Accepted Solutions
Moderator v-sihou-msft
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,

View solution in original post

2 REPLIES 2
Moderator v-sihou-msft
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,

View solution in original post

Highlighted
FergalK Regular Visitor
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])
)
))

Helpful resources

Announcements
New Kudos Given Badges Coming

New Kudos Given Badges Coming

We're rolling out new Kudos Given badges. Find out how many Kudos you've given.

Microsoft Implementation for Communities Wins Award

Microsoft Implementation for Communities Wins Award

Learn about the award-winning innovation that was implemented across Microsoft’s Business Applications Communities.

Power Platform World Tour

Power Platform World Tour

Find out where you can attend!

Top Kudoed Authors (Last 30 Days)
Users online (1,770)