topic Re: Multiple Selection on Dynamic Financial Quarter by Customer in DAX Commands and Tips

Multiple Selection on Dynamic Financial Quarter by Customer

tclaus — Wed, 11 May 2022 22:43:52 GMT

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 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 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!

Re: Multiple Selection on Dynamic Financial Quarter by Customer

amitchandak — Thu, 12 May 2022 02:00:14 GMT

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

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

Re: Multiple Selection on Dynamic Financial Quarter by Customer

tclaus — Thu, 12 May 2022 19:59:06 GMT

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!

Re: Multiple Selection on Dynamic Financial Quarter by Customer

v-yanjiang-msft — Wed, 18 May 2022 06:25:30 GMT

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

Re: Multiple Selection on Dynamic Financial Quarter by Customer

v-yanjiang-msft — Wed, 01 Jun 2022 02:17:59 GMT

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

Re: Multiple Selection on Dynamic Financial Quarter by Customer

tclaus — Mon, 06 Jun 2022 17:16:11 GMT

Hi v-yanjiang-msft,

Yes, sorry. Thanks for your help!