Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
bilalkhokar73
Helper IV
Helper IV

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 IV
Helper IV

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

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
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.