Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
@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))
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)
)
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
47 | |
26 | |
19 | |
14 | |
10 |
User | Count |
---|---|
58 | |
50 | |
44 | |
19 | |
18 |