cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
bilalkhokar73
Helper III
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
bilalkhokar73
Helper III
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 

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

Did I answer your question? Mark my post as a solution! Appreciate your Kudos!! !!
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!
V-pazhen-msft
Community Support
Community Support

@bilalkhokar73 

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.
Vpazhenmsft_0-1626853478665.png

 

 

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

Helpful resources

Announcements
2022 Release Wave 1 760x460.png

2022 Release Wave 1 Plan

Power Platform release plan for the 2022 release wave 1 describes all new features releasing from April 2022 through September 2022.

Power BI December 2021 Update_carousel 768x460.jpg

Check it Out!

Click here to read more about the December 2021 Updates!

Top Solution Authors