Forum Discussion

Lamdasquare's avatar
Lamdasquare
Regular Visitor
2 years ago
Solved

Last 8 dates/weeks of data

Hello Forum!  

 

Newbie here, been spinning my head on this for a few days so thought i'd post here.  Trying to pull the last 8 dates/weeks from my dataset.  I'm not sure if I have to create a date table to do this or if some form of calculate will make it work - i've been trying a few calculate statements, datesinperiod but it seems to pull all 9 values that I currently have instead of just the 8 I think it should so my forumla seems off.  

What my table has is a date column, sales, geo, and category.  The date is not daily but a weekly date - so it gives every 7th day date - from Monday I believe so the dates would look like, for example, Jan 1, Jan 8, Jan 15, Jan 22.... etc.  

I'm trying to put together a visual that will show the last 8 dates/weeks of data broken out by category and this will change obviously from week to week as a new weeks worth of data comes in once its refreshed so it has to be rolling.  Is it possible to do some sort of calc on the date field to just do a -8 and get it to work?  I even thought of using a weeknum column but I wasn't able to get that to work either.

 

Thanks for any responses/help!!

Need Help
  • ryan_mayu's avatar
    ryan_mayu
    2 years ago

    you can create a column

     

    Column = if('Table'[Date]>=(max('Table'[Date])-56),"y")
     
     
    then you add the column to the visual filter and select y
     
     
    pls see the attachment below

6 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    Hi Lamdasquare 

     

    ryan_mayu Thank you very much for your prompt reply. Please allow me to share some other methods here.

     

    Create a measure to dynamically sort dates. And look for the top eight data.

     

    Last 8 weeks Sales = 
var _rank = RANKX(ALL('Table'), CALCULATE(MAX('Table'[Date])),, DESC, Dense)
RETURN
IF(
    _rank <= 8,
    SELECTEDVALUE('Table'[Sales]),
    BLANK()
)

     

    Here is the result.

     

     

    Regards,

    Nono Chen

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

  • ryan_mayu's avatar
    ryan_mayu
    Icon for Super User rankSuper User
    2 years ago

    could you pls provide some sample data and expected output?

    • Lamdasquare's avatar
      Lamdasquare
      Regular Visitor
      2 years ago

      Sure - here is a sample table:  We will assume for this table the date is the week after 3/11 so 3/11 is the last updated value.  The first few give you an idea of the data - for simplicity sake I just used the next weeks dates to show you a table of 11 weeks of data and I am looking to pull the last 8 weeks of data from 3/11 backwards.  

       

      DateGeoCategorySales
      1/1/2024US14
      1/1/2024Canada15.3
      1/1/2024England12
      1/1/2024France11
      1/1/2024US22
      1/1/2024Canada23.4
      1/1/2024England26
      1/1/2024France29
      1/7/2024US31
      1/7/2024Canada31
      1/7/2024England32
      1/7/2024France33
      1/14/2024US14
      1/14/2024Canada15.3
      1/14/2024England12
      1/14/2024France11
      1/21/2024US22
      1/28/2024Canada23.4
      2/4/2024England26
      2/11/2024France29
      2/18/2024US31
      2/25/2024Canada31
      3/4/2024England32
      3/11/2024France33


      So I would expect to pull all data from 3/11 back to 1/21 as that is 8 weeks worth of data.  So then when I put it into a line chart visual it will have the categories going up/down in zig zaggy lines by Geo with the 8 dates showing on the bottom to show how each category is changing by week.  

      • ryan_mayu's avatar
        ryan_mayu
        Icon for Super User rankSuper User
        2 years ago

        you can create a column

         

        Column = if('Table'[Date]>=(max('Table'[Date])-56),"y")
         
         
        then you add the column to the visual filter and select y
         
         
        pls see the attachment below