Auto select previous date

[yannick90]

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 🙂

[danextian]

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 · Accepted solution]

hi @danextian ,

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

[yannick90]

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]

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

[yannick90]

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]

What i was trying to say was, if today is Jan 30, are you trying to compare Jan 30 with Jan 23 or the from Jan 23 to 30 vs Jan 16 to Jan 22? 

[yannick90]

@danextian I see now, I am trying to compare Jan 30 with Jan 23

[danextian]

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]

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