Forum Discussion

tclaus's avatar
tclaus
Frequent Visitor
4 years ago
Solved

Multiple Selection on Dynamic Financial Quarter by Customer

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 Jul...
  • v-yanjiang-msft's avatar
    v-yanjiang-msft
    4 years ago

    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

amitchandak's avatar
amitchandak
Icon for Super User rankSuper User
4 years ago

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's avatar
tclaus
Frequent Visitor
4 years ago

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's avatar
    v-yanjiang-msft
    Icon for Community Support rankCommunity Support
    4 years ago

    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