Forum Discussion

yannick90's avatar
yannick90
Frequent Visitor
4 years ago
Solved

Auto select previous date

Hello guys,

I'm coming up to you with an issue that has been frying my brain lately. Long story short, right now I'm comparing the data of two dates (this week vs previous week) hence on my calendar slicer, I have to click on two dates: but the objective of the user is to just click on one date (April 10) and it automatically compares with the data of the previous date (April 3rd)

 

Any suggestions will be more than welcomed

Thank you 🙂

Need Help
  • yannick90's avatar
    yannick90
    4 years ago

    hi danextian ,

     

    Thanks for all the support. The user decided to go with a different approach instead. 

9 Replies

  • danextian's avatar
    danextian
    Super User
    4 years ago

    Hi yannick90 ,

     

    Please try this

    Value Last Week = 
CALCULATE (
    [Sum of Values],
    FILTER ( ALL ( Dates ), Dates[Date] = MAX ( Dates[Date] ) - 7 )
)

     

    If this doesn't work, please post a sample data and your dates table.

    • yannick90's avatar
      yannick90
      Frequent Visitor
      4 years ago

      Hi danextian ,

       

      this is what I typed:

       

      CalcInvLastWeek RsmC = CALCULATE(SUM('table'[data)]),
      FILTER(ALLSELECTED('table'),'table'[WeekRank]=MAX('table'[WeekRank])-7))
       
      This is what I get when I filtered May 22nd:
       

       

      I also tried the below but no luck:

       

      CalcInvLastWeek RsmC = CALCULATE(SUM('table'[data)]),
      FILTER(ALLSELECTED('table'),'table'[date]=MAX('table'[date])-7))

       

      thanks you

  • yannick90's avatar
    yannick90
    Frequent Visitor
    4 years ago

    Hi danextian, 

    Here is a sample of my date tables:

    main calendar

    additional columns:

     

    if I select May 1st and April 24th, below is the result:

     

    but I want to get the same result just by selecting May 1st

    • danextian's avatar
      danextian
      Super User
      4 years ago

      Are you trying to compute for the value in the last 7 days a week ago or the value 7 days ago?

      • yannick90's avatar
        yannick90
        Frequent Visitor
        4 years ago

        Hello danextian,

         

        I'm a bit confused by your statement. let me give a bit of context, the file is updated every monday. For example it was updated on April 24th, then May 1st and so on. So the scenario goes like this (take the data of "this week which is May 1st" and compare it to the data of last week which is April 24th

         

        There is already a formula in place that is giving me the value of this week (2,8 millions) and last week (2,9 millions). 

        Now the objective is to get the value of difference and variation by just selecting this week's date rather than selecting this week and last week

         

        Let me know if it answers your question

        Thanks  

  • danextian's avatar
    danextian
    Super User
    4 years ago

    Hi yannick90 ,

     

    Have you tried my approach in the PBIX i've previously shared? If that doesn't work, please share sanitized copy of your pbix (anonimized data, a representation of your data model) ?

    • yannick90's avatar
      yannick90
      Frequent Visitor
      4 years ago

      hi danextian ,

       

      Thanks for all the support. The user decided to go with a different approach instead.