Forum Discussion

NP755's avatar
NP755
New Member
3 years ago

extracting dynamic quaterly

hi Expert 

i am trying to extract the quraterly and fiscal data but my qtrly and fiscal data are showing as same

dax for qtrly: 

 

Current Quarter Closed PIR Fraud Exposure = 
Var _gsi= CALCULATE([PIR EXPOSURE],'PIR-Case-POV'[Stage] ="Closed - No Further Action Required")
VAR _maxyear=MAX('Date'[Fiscal Year])
VAR _maxdate=MAX('PIR-Case-POV'[PIRReferralDate])
VAR _mxqtr=ROUNDUP(MONTH(_maxdate)/3,0) 
RETURN
CALCULATE(_gsi,'Date'[Fiscal Year]=_maxyear,'Date'[Fiscal Quarter]=_mxqtr)

 

in this code i am getting the fiscal year data instead of qtrly data. Is there any way to have this qtr dynamic like lets say if we want to have the previous qtr data. 

 

thank you again in advance

daX ERROR

2 Replies

  • amitchandak's avatar
    amitchandak
    Super User
    3 years ago

    NP755 , depending on month your qtr start change the logic

     

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

     

     

    // Non standard - Start in Feb
    QTD =
    var _max = if(isfiltered('Date'),MAX( 'Date'[Date]) , today())
    var _mod = mod(Month(_max),3)
    var _min = eomonth(_max,-1* Switch(True(), _mod =2, 1, _mod =1, 3,2))+1
    return
    CALCULATE([net] ,Filter('Date','Date'[Date]>= _min && 'Date'[Date] <= _max))

    • NP755's avatar
      NP755
      New Member
      3 years ago

      hi Amit 

      thank you for your time, 

      my fiscal year is from nov to oct

       

      i have a date coded as below and based on that i was retrieving the qtry data

      Date = 
//************** Script developed by RADACAD - edition: July 2021 
//************** set the variables below for your custom date table setting 
var _MinDate=Min('PIR-Case-POV'[PIRReferralDate])
var _MaxDate=MAX('PIR-Case-POV'[PIRReferralDate])
var _fromYear=YEAR(_MinDate)// set the start year of the date dimension. dates start from 1st of January of this year
var _toYear=YEAR(_MaxDate)
var _startOfFiscalYear=11 // set the month number that is start of the financial year. example; if fiscal year start is July, value is 7
//************** 
var _today=TODAY()
return
ADDCOLUMNS(
    CALENDAR(
                DATE(_fromYear,1,1),
                DATE(_toYear,12,31)
),
"Year",YEAR([Date]),
"Start of Year",DATE( YEAR([Date]),1,1),
"End of Year",DATE( YEAR([Date]),12,31),
"Month",MONTH([Date]),
"Start of Month",DATE( YEAR([Date]), MONTH([Date]), 1),
"End of Month",EOMONTH([Date],0),
"Days in Month",DATEDIFF(DATE( YEAR([Date]), MONTH([Date]), 1),EOMONTH([Date],0),DAY)+1,
"Year Month Number",INT(FORMAT([Date],"YYYYMM")),
"Year Month Name",FORMAT([Date],"YYYY-MMM"),
"Day",DAY([Date]),
"Day Name",FORMAT([Date],"DDDD"),
"Day Name Short",FORMAT([Date],"DDD"),
"Day of Week",WEEKDAY([Date]),
"Day of Year",DATEDIFF(DATE( YEAR([Date]), 1, 1),[Date],DAY)+1,
"Month Name",FORMAT([Date],"MMMM"),
"Month Name Short",FORMAT([Date],"MMM"),
"Quarter",QUARTER([Date]),
"Quarter Name","Q"&FORMAT([Date],"Q"),
"Year Quarter Number",INT(FORMAT([Date],"YYYYQ")),
"Year Quarter Name",FORMAT([Date],"YYYY")&" Q"&FORMAT([Date],"Q"),
"Start of Quarter",DATE( YEAR([Date]), (QUARTER([Date])*3)-2, 1),
"End of Quarter",EOMONTH(DATE( YEAR([Date]), QUARTER([Date])*3, 1),0),
"Week of Year",WEEKNUM([Date]),
"Start of Week", [Date]-WEEKDAY([Date])+1,
"End of Week",[Date]+7-WEEKDAY([Date]),
"Fiscal Year",if(_startOfFiscalYear=1,YEAR([Date]),YEAR([Date])+ QUOTIENT(MONTH([Date])+ (13-_startOfFiscalYear),13)),
"Fiscal Quarter",QUARTER( DATE( YEAR([Date]),MOD( MONTH([Date])+ (13-_startOfFiscalYear) -1 ,12) +1,1) ),
"Fiscal Month",MOD( MONTH([Date])+ (13-_startOfFiscalYear) -1 ,12) +1,
"Day Offset",DATEDIFF(_today,[Date],DAY),
"Month Offset",DATEDIFF(_today,[Date],MONTH),
"Quarter Offset",DATEDIFF(_today,[Date],QUARTER),
"Year Offset",DATEDIFF(_today,[Date],YEAR)
)