Forum Discussion

bilalkhokar73's avatar
bilalkhokar73
Helper IV
5 years ago

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

Need Help

4 Replies

  • bilalkhokar73's avatar
    bilalkhokar73
    Helper IV
    5 years ago

    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 

    • amitchandak's avatar
      amitchandak
      Super User
      5 years ago

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

  • Anonymous's avatar
    Anonymous
    Not applicable
    5 years ago

    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.

     

     

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

    • bilalkhokar73's avatar
      bilalkhokar73
      Helper IV
      5 years ago

      Anonymous 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