Forum Discussion

devika's avatar
devika
Icon for Helper II rankHelper II
3 years ago
Solved

Dynamically display last x quarters - line graph

Hi all,

I need to display the last 19 quarters from the current quarter dynamically.

 

The current quarter is 20231; the line graph should start showing from 20182 to 20231 and

When the new quarter starts in 20232, the line graph should show from 20183 to 20232 and so on.

The dataset is connected directly to the Oracle SQL server, storing 130000 rows.

Currently, in the measure, future quarters are truncated.

CHECK=

VAR MinDate =MinX(allselected(Merge[CASE_DATE]),Merge[CASE_DATE])

VAR MaxDate = MaxX(allselected(Merge[CASE_DATE]),MergeCASE_DATE])

RETURN CALCULATE(IF(MAX('Merge_Annual_Mx_Vss'[CASE_DATE]) < MinDate || MAX(Merge[CASE_DATE]) > MaxDate,BLANK(),value))

 

I have looked into this thread, but it is slow to churn. Rolling QuarterEnd is not working.

https://community.powerbi.com/t5/Desktop/Dynamically-display-last-8-quarters/m-p/394240#M179827

 

Any help would be grateful.

Need Help
  • devika's avatar
    devika
    3 years ago

    Thank you for your time,

    I made a slight tweak as it wasn't displaying the current quarter.

     

    Last Qtr Today =
    var _today = today()
    var _max = eomonth(_today, -1*if( mod(Month(_today),3) =0,3,mod(Month(_today),3)))
    var _min = eomonth(_max,-3*8)+1

    var _maxDate = [MaxCaseDate]


    return
    CALCULATE([Net], FILTER('Date','Date'[Date] >=_min && 'Date'[Date] <=_maxDate)

    ))

2 Replies

  • amitchandak's avatar
    amitchandak
    Icon for Super User rankSuper User
    3 years ago

    devika , In case there is no date filter

     

    last 8 qtr

     

    Last Qtr Today =
    var _today = today()
    var _max = eomonth(_today, -1*if( mod(Month(_today),3) =0,3,mod(Month(_today),3)))
    var _min = eomonth(_max,-3*8)+1
    return
    CALCULATE([Net], FILTER('Date','Date'[Date] >=_min && 'Date'[Date] <= _max))

     

    if you want select a qtr in slicer, then that slicer should come from an independent date table 

     

     

    //Date1 is independent Date table, Date is joined with Table
    new measure =
    var _max = maxx(allselected(Date1),Date1[Date])
    var _min = eomonth(_max, -24) +1
    return
    calculate( sum(Table[Value]), filter('Date', 'Date'[Date] >=_min && 'Date'[Date] <=_max))

     

    Need of an Independent Date Table:https://www.youtube.com/watch?v=44fGGmg9fHI

    • devika's avatar
      devika
      Icon for Helper II rankHelper II
      3 years ago

      Thank you for your time,

      I made a slight tweak as it wasn't displaying the current quarter.

       

      Last Qtr Today =
      var _today = today()
      var _max = eomonth(_today, -1*if( mod(Month(_today),3) =0,3,mod(Month(_today),3)))
      var _min = eomonth(_max,-3*8)+1

      var _maxDate = [MaxCaseDate]


      return
      CALCULATE([Net], FILTER('Date','Date'[Date] >=_min && 'Date'[Date] <=_maxDate)

      ))