Multiple Selection on Dynamic Financial Quarter by Customer

[tclaus]

Hello,

My coworker and I are trying to create a quarterly report that has the ability to slice by customer and reflect their financial year. For example one customers financial year starts in July so Quarter 1 for them should be July 1 - September 30. We are able to get this to work for one specific quarter already but the issue becomes when we introduce the previous quarter. We are wondering if there is a way to get the current quarter based on the customers financial year start month and the previous quarter dynamically. Here is a preview of our report page: 

Here are the DAX Measures as well:

FY Start Date =

VAR FinancialStartMonth = MIN(Customer_List[FY.Start.Month])

VAR FiscalYearSelected = SELECTEDVALUE('QuarterIDs'[Year])

VAR QuarterNum = SELECTEDVALUE(QuarterIDs[Quarter])

VAR StartDate = SWITCH(QuarterNum, 1, DATE(FiscalYearSelected, FinancialStartMonth, 1), 2, DATE(FiscalYearSelected, FinancialStartMonth+3, 1), 3, DATE(FiscalYearSelected, FinancialStartMonth+6, 1), 4, DATE(FiscalYearSelected, FinancialStartMonth+9, 1))

RETURN

StartDate

 ////////

FY End Date =

VAR FinancialStartMonth = MIN(Customer_List[FY.Start.Month])

VAR FiscalYearSelected = SELECTEDVALUE('QuarterIDs'[Year])

VAR QuarterNum = SELECTEDVALUE(QuarterIDs[Quarter])

VAR StartDate = SWITCH(QuarterNum, 1, DATE(FiscalYearSelected, FinancialStartMonth, 1), 2, DATE(FiscalYearSelected, FinancialStartMonth+3, 1), 3, DATE(FiscalYearSelected, FinancialStartMonth+6, 1), 4, DATE(FiscalYearSelected, FinancialStartMonth+9, 1))

VAR EndDate = DATE(YEAR(StartDate), MONTH(StartDate)+3,1) -1

RETURN

EndDate

 ////////

Total Amount = CALCULATE(SUM(Raw_Data[Amount]),

FILTER('Raw_Data', [Date]))

 //////

Amounts by FY =

VAR FinancialStartMonth = MIN(Customer_List[FY.Start.Month])

VAR FiscalYearSelected = SELECTEDVALUE(QuarterIDs[Year])

VAR QuarterNum = SELECTEDVALUE(QuarterIDs[Quarter])

VAR StartDate = SWITCH(QuarterNum, 1, DATE(FiscalYearSelected, FinancialStartMonth, 1), 2, DATE(FiscalYearSelected, FinancialStartMonth+3, 1), 3, DATE(FiscalYearSelected, FinancialStartMonth+6, 1), 4, DATE(FiscalYearSelected, FinancialStartMonth+9, 1))

VAR EndDate = DATE(YEAR(StartDate), MONTH(StartDate)+3,1) - 1

RETURN

IF(HASONEVALUE(Dates[Date]),

IF(AND(MIN(Dates[Date]) >= StartDate, MAX(Dates[Date]) < EndDate), [Total Amount], BLANK()),

CALCULATE([Total Amount],

FILTER(ALL(Dates[Date]), Dates[Date] >= StartDate && Dates[Date] < EndDate)))

///////// 

Big thanks to @EnterpriseDNA for his multiple financial years solution.

Thank you for reading this and we appreciate any information or assistance you can provide!

[v-yanjiang-msft]

Hi @tclaus ,

Is your problem solved?? If so, Would you mind accept the helpful replies as solutions? Then we are able to close the thread. More people who have the same requirement will find the solution quickly and benefit here. Thank you.

Best Regards,
Community Support Team _ kalyj

[tclaus]

Hi @v-yanjiang-msft,

Yes, sorry. Thanks for your help!

[amitchandak]

@tclaus , You can try meausres like

Last Qtr =
var _max1 = if(isfiltered('Date'),MAX( 'Date'[Date]) , today())
var _max = eomonth(_max,-1* if( mod(Month(_max),3) =0,3,Month(_max)))
var _min = eomonth(_min,-3)+1
return
CALCULATE([net] ,DATESBETWEEN('Date'[Date],_min,_max))

or

Last Qtr =
var _max1 = if(isfiltered('Date'),MAX( 'Date'[Date]) , today())
var _max = eomonth(_max,-1* if( mod(Month(_max),3) =0,3,Month(_max)))
var _min = eomonth(_min,-3)+1
return
CALCULATE([net] ,Filter('Date','Date'[Date]>= _min && 'Date'[Date] <= _max))
//Or use all
CALCULATE([net] ,Filter(all('Date'[Date]),'Date'[Date]>= _min && 'Date'[Date] <= _max))

Current

Qtr =
var _max = if(isfiltered('Date'),MAX( 'Date'[Date]) , today())
var _min = eomonth(_max,-1* if( mod(Month(_max),3) =0,3,Month(_max)))+1
return
CALCULATE([net] ,Filter('Date','Date'[Date]>= _min && 'Date'[Date] <= _max))

or

Qtr =
var _max = if(isfiltered('Date'),MAX( 'Date'[Date]) , today())
var _min = eomonth(_max,-1* if( mod(Month(_max),3) =0,3,Month(_max)))+1
return
CALCULATE([net] ,DATESBETWEEN('Date'[Date],_min,_max))

[tclaus]

Hi @amitchandak 

This solution seems to be exactly what we are after. However, we are having trouble understanding some of your variables. When I try to define var _max = eomonth(_max,-1* if( mod(Month(_max),3) =0,3,Month(_max))). The DAX throws an error: "Failed to resolve name '_min'. It is not a valid table, variable, or function name." How is it possible to use a variable within a variable on the same line? It's grayed out and underlined in red within my measure when I'm trying to replicate this. Thanks for all your help!

[v-yanjiang-msft · Accepted solution]

Hi @tclaus ,

Modify @amitchandak 's formula like this:

Last Qtr =
var _max1 = if(isfiltered('Date'),MAX( 'Date'[Date]) , today())
var _max = eomonth(_max1,-1* if( mod(Month(_max1),3) =0,3,Month(_max1)))
var _min = eomonth(_max,-3)+1
return
CALCULATE([net] ,DATESBETWEEN('Date'[Date],_min,_max))

or

Last Qtr =
var _max1 = if(isfiltered('Date'),MAX( 'Date'[Date]) , today())
var _max = eomonth(_max1,-1* if( mod(Month(_max1),3) =0,3,Month(_max1)))
var _min = eomonth(_max,-3)+1
return
CALCULATE([net] ,Filter('Date','Date'[Date]>= _min && 'Date'[Date] <= _max))
//Or use all
CALCULATE([net] ,Filter(all('Date'[Date]),'Date'[Date]>= _min && 'Date'[Date] <= _max))

Best Regards,
Community Support Team _ kalyj