Forum Discussion

piopol's avatar
piopol
Frequent Visitor
4 years ago

Reduce table with filter and export to csv

Hi All, 

I am trying to perform some stock history analysis for as few tickers.
What I am facing currently is to reduce and export the table to show only rows from a particular date with 30 days increment for each ticker.
My data is stored here https://drive.google.com/file/d/1v0rf6Au751D8fR-8y1ewPwSnKUgtrE85/view?usp=sharing 
This is what I am targeting:

Date;TICKER;SMA200
02.01.2019;MRNA;$15,33
02.01.2019;MSFT;$101,12

02.02.2019;MRNA;$15,415
02.02.2019;MSFT;$99,26
02.03.2019;MRNA;$15,415
02.03.2019;MSFT;$99,26

......

If a date with value does not exist (bank holiday or weekend) it should take the following date.

I know it should be done with variables and filters, but I am still learning how those things works.


Thank you in advance for any ideas. 

Piotr

 

Need Help

3 Replies

  • amitchandak's avatar
    amitchandak
    Super User
    4 years ago

    piopol , Create a Rank column on Date

     

    Rank = Rankx(Table, [date], ,asc, dense)

     

    Then create a measure like this and use that in visual or visual level filter

     

    Measure =

    var _max = Maxx(allselected(Table), Table[Rank])

    return

    calculate(sum(Table[SMA200]) , filter(Table, Table[Rank] >= _max -30 && Table[Rank] <=_max))

     

     

    or

     

     

    Measure =

    var _max = Maxx(allselected(Table), Table[Rank])

    return

    calculate(sum(Table[SMA200]) , filter(all(Table), Table[Rank] >= _max -30 && Table[Rank] <=_max))

  • v-jingzhang's avatar
    v-jingzhang
    Community Support
    4 years ago

    Hi piopol 

     

    Sorry I don't understand what is the expected result? What do you mean by "show only rows from a particular date with 30 days increment for each ticker"? What is the particular date? Is it a fixed date or a dynamic date? Do you mean to show the values of the next 30 days after that date? Can you please use some examples to elaborate the expected result?

     

    Best Regards,
    Community Support Team _ Jing

    • piopol's avatar
      piopol
      Frequent Visitor
      4 years ago

      So I have data for each day for a few tickers. I want to filter it just for a particular date, i.e. each 15th day of the month. The problem is that sometimes the 15th is Saturday or Sunday, or the stock market didn't work (bank holiday or other reason). 
      Currently, I am filtering and exporting it manually, checking whether data exist or not. If not, I am taking the next date, if the following date is empty I am taking the date after the next date and so on.