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= CALCULAT...
daX ERROR
amitchandak's avatar
amitchandak
Icon for Super User rankSuper 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)
)