Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
I am facing one issue when I am making growth degrwoth for quarter 3 , degrwoth shwoing high
I need quarter 3 formula till date , means quarter 3 start from 1july to 30sept 2020 and 2021, so for 1 july to till date i need quarter 2020 and quarter 2021 (today's date is 19july 2021)
I need Q3 2021 as 1july to till date 2021 19july 2021
Q3 2020 as 1july to till date 2020 19 july 2020
My current formula
Q32021 = var _max = date(year(today()),9 ,30) var _min = date(year(today()),7 ,1)
return CALCULATE(sum(sales[Amt]), FILTER(ALL(DATE),DATE[date] >=_min && DATE[date] <= _max) )
Q32020 = var _max = date(year(today())-1,9 ,30) var _min = date(year(today())-1,7 ,1)
return CALCULATE(sum(sales[Amt]), FILTER(ALL(DATE),DATE[date] >=_min && DATE[date] <= _max) )
Q3 20V21% = var _grow=DIVIDE([Q32021]-[Q32020],[Q32020],BLANK())
return IF([Q32021]>[Q32020],ABS(_grow),-ABS(_grow))
I am facing one issue when i am making growth degrwoth for quarter 3 , degrwoth showing high
I need quarter 3 till date , means quarter 3 start from 1july to 30sept 2020 and 1 july to 30Sept2021,
so for 1 july to till date (todays date is 20 july 2021 ) So i need
Quarter3 2021 = 1 july to 20 july 2021
Quarter3 2020 = 1 july to 20 july 2020
when 30august will come then formula will shwo for till 1july tto 30 aug then new quarter will come
Q32021 = var _max = date(year(today()),9 ,30) var _min = date(year(today()),7 ,1)
return CALCULATE(sum(sales[Amt]), FILTER(ALL(DATE),DATE[date] >=_min && DATE[date] <= _max) )
Q32020 = var _max = date(year(today())-1,9 ,30) var _min = date(year(today())-1,7 ,1)
return CALCULATE(sum(sales[Amt]), FILTER(ALL(DATE),DATE[date] >=_min && DATE[date] <= _max) )
Q3 20V21% = var _grow=DIVIDE([Q32021]-[Q32020],[Q32020],BLANK())
return IF([Q32021]>[Q32020],ABS(_grow),-ABS(_grow)) @amitchandak @Fowmy @TomMartens
@bilalkhokar73 , Time intelligence function should help
QTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESQTD(('Date'[Date])))
Last year same QTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESQTD(dateadd('Date'[Date],-1,Year)))
Or without TI functions
QTD Today=
var _max = maxx(allselected('Date'), 'Date'[Date])
var _month = mod(month(_max),3)
var _min = date(year(_max),month(_max) -1* if(_month=0,3,_month) ,1)
var _day = _max
return
CALCULATE(sum('order'[Qty]), FILTER(ALL('Date'),'Date'[Date] >=_min && 'Date'[Date] <= _day) )
LYQTD Today=
var _max = maxx(allselected('Date'), 'Date'[Date])
var _month = mod(month(_max),3)
var _min = date(year(_max)-1,month(_max) -1* if(_month=0,3,_month) ,1)
var _day = date(year(_max)-1, month(_max),day(_max))
return
CALCULATE(sum('order'[Qty]), FILTER(ALL('Date'),'Date'[Date] >=_min && 'Date'[Date] <= _day) )
Create below 2 measures for 2020 and 2021. Replace the table name accordingly and use the same measure to compare 2020 and 2021. You may also replace Quarter Number from "3" to Quarter(Today()).
Q32021 = CALCULATE(SUM('SALES'[Amt]),FILTER(ALLSELECTED('DATE'),[Date].[QuarterNo]=Quarter(Today()) && [Date].[Year]=YEAR(TODAY()) && [Date]<=TODAY()))
Q32020 = CALCULATE(SUM('SALES'[Amt]),FILTER(ALLSELECTED('DATE'),[Date].[QuarterNo]=Quarter(Today()) && [Date]<=DATE(YEAR(TODAY())-1,MONTH(TODAY()),DAY(TODAY()))))
Paul Zheng _ Community Support Team
If this post helps, please Accept it as the solution to help the other members find it more quickly.
@V-pazhen-msft I saw here your data in power bi shwoign values for August , sept, oct 2020 also
But let say today is 25 july then i need dynamic measure from 01 july -25july 2020 to 01 july-25July 2021
User | Count |
---|---|
128 | |
108 | |
99 | |
65 | |
62 |
User | Count |
---|---|
136 | |
113 | |
102 | |
71 | |
60 |