Forum Discussion

Nimai123's avatar
Nimai123
Post Patron
5 years ago

Index Measure for Rolling

I want my Rolling ACV Measure to be displayed from FY17-Q4, so I created a measure Index for Rolling where I wanted it to behave in a manner which makes the ACV Measure to start from FY17-Q4

 

Index for rolling = CALCULATE(DISTINCTCOUNT(derived_dimdate_2[fiscal_quarter_year_qtd]),DATESINPERIOD(derived_dimdate_2[fulldate],ENDOFMONTH(derived_dimdate_2[fulldate]),-4,QUARTER),NOT(ISBLANK(bookings_reporting_view[Maintenance End Date])))
 
 
Relationship
 
Previously Worked well for me in a different dataset but require to be implemented same here
 
 
Need Help

3 Replies

  • amitchandak's avatar
    amitchandak
    Super User
    5 years ago

    Nimai123 , Create a rolling formula that checks distinctcount of month year in fact table <=12, if so do not show data.

  • Greg_Deckler's avatar
    Greg_Deckler
    Community Champion
    5 years ago

    Nimai123 I've been looking this and I still don't know what you want exactly. What shows what you want as the output? Can you post sample data as text and expected output from sample data and why you should get that result? Going to need to mock this up to figure it out.

     

    • Nimai123's avatar
      Nimai123
      Post Patron
      5 years ago

      Hello Greg_Deckler 

       

      I need the output in this manner where my table is starting to display from 17-Q4

      fiscal_quarter_year_qtdCancelled ACVRolling ACV
      FY17 - Q4$3,622,089.834393159.24
      FY18 - Q1$6,757,638.3711100011.31
      FY18 - Q2$8,664,261.8919628983.49
      FY18 - Q3$12,289,999.5731333989.66
      FY18 - Q4$11,132,466.4138844366.24
      FY19 - Q1$9,840,930.3341927658.2
      FY19 - Q2$9,935,995.7643199392.07
      FY19 - Q3$14,463,339.5445372732.04
      FY19 - Q4$11,474,599.4845714865.11
      FY20 - Q1$7,748,718.8743622653.65
      FY20 - Q2$10,039,563.1443726221.03
      FY20 - Q3$18,549,398.1947812279.68
      FY20 - Q4$15,284,933.3151622613.51
      FY21 - Q1$9,998,708.7853872603.42
      FY21 - Q2$9,488,384.4253321424.7
      FY21 - Q3$605,659.0635377685.57
      FY21 - Q4$83,040.4820175792.74
      FY22 - Q1$3,975.3310181059.29
      FY22 - Q2$37,403.87730078.74
      FY22 - Q3$112,312.56236732.24
      FY22 - Q4$24,723.91178415.67
      FY23 - Q1$0174440.34
      FY23 - Q2$0137036.47
      FY23 - Q3$1,101.4225825.33
      FY23 - Q4$01101.42
      FY24 - Q1$01101.42
      FY24 - Q2 1101.42
      FY24 - Q3 0

       

      Sample data

       

      fiscal_quarter_year_qtdCancelled ACVRolling ACV
      FY15 - Q3$47,748.9447748.94
      FY15 - Q4$17,095.7764844.71
      FY16 - Q1$33,935.7398780.44
      FY16 - Q2$13,805.46112585.9
      FY16 - Q3$166,304.32231141.28
      FY16 - Q4$26,638.80240684.31
      FY17 - Q1$50,786.30257534.88
      FY17 - Q2$135,289.71379019.13
      FY17 - Q3$584,993.40797708.21
      FY17 - Q4$3,622,089.834393159.24
      FY18 - Q1$6,757,638.3711100011.31
      FY18 - Q2$8,664,261.8919628983.49
      FY18 - Q3$12,289,999.5731333989.66
      FY18 - Q4$11,132,466.4138844366.24
      FY19 - Q1$9,840,930.3341927658.2
      FY19 - Q2$9,935,995.7643199392.07
      FY19 - Q3$14,463,339.5445372732.04
      FY19 - Q4$11,474,599.4845714865.11
      FY20 - Q1$7,748,718.8743622653.65
      FY20 - Q2$10,039,563.1443726221.03
      FY20 - Q3$18,549,398.1947812279.68
      FY20 - Q4$15,284,933.3151622613.51
      FY21 - Q1$9,998,708.7853872603.42
      FY21 - Q2$9,488,384.4253321424.7
      FY21 - Q3$605,659.0635377685.57
      FY21 - Q4$83,040.4820175792.74
      FY22 - Q1$3,975.3310181059.29
      FY22 - Q2$37,403.87730078.74
      FY22 - Q3$112,312.56236732.24
      FY22 - Q4$24,723.91178415.67
      FY23 - Q1$0174440.34
      FY23 - Q2$0137036.47
      FY23 - Q3$1,101.4225825.33
      FY23 - Q4$01101.42
      FY24 - Q1$01101.42
      FY24 - Q2 1101.42
      FY24 - Q3 0
      FY24 - Q4 0

       

      I have achieved this same scenario before with the help of index measure but I don't know why I am not able to get it this time.

       

       

      Pbix file

      https://drive.google.com/drive/folders/1Wz51npWQ-8gZjgJ_fXUtPbhWYIW_mWdl?usp=sharing