cancel
Showing results for
Did you mean:  Helper III

Dynamic Quarter Till date

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))

4 REPLIES 4  Helper III

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  Super User

@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) )

Dashboard of My Blogs !! Connect on Linkedin
Want To Learn Power BI
Learn Power BI Beginners !! Advance Power BI Concepts !! Power BI For Tableau User !! Learn Power BI in Hindi !!
Proud to be a Super User!  Community Support

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()))))

Check the example if needed. Paul Zheng _ Community Support Team
If this post helps, please Accept it as the solution to help the other members find it more quickly.  Helper III

@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   