# Dax Calculation for choose N specific date

Hi every One
I Have a problem
I Have a requirement in my Dashborad. I Have a Measure like Sales Amount. I have a slicer for date. User Choose a date (For Example 5/6/2021) and My requirement is to show four value for 7, 14,21, 28 days ago. only four point in chart. for example user choose 5/5/2021 and see 4 point for four date. 5 may, 28 april, 21 april, 14 april.
and is it possible to make it dynamic? user select a date and a number (in this example 4) to see four point and or 5 and see 5 point in chart (5 may, 28 april, 21 april, 14 april, 7 april)

thanks for your consideration

Hi @HosseinVossoughi. Shouldnâ€™t be a problem at all. I did something not too dissimilar to this in my Top N and Average of Others example recently. Hope it helps. Greg

1 Like

Thanks @Greg . I watched it But I couldnâ€™t find similarity of these two Challenges. I want to filter 4 date dynamically.

@HosseinVossoughi Excellent Question, good candidate for a video!

Hashem.pbix (161.0 KB)
Hopefully I understood the requirements correctly then you need to create a disconnected table and use this pattern:

``````Hashem =
VAR ValuesToShow = 4
VAR DisSelectedDate =
SELECTEDVALUE ( DisDate[Date] )
VAR DatesToShow =
GENERATESERIES ( DisSelectedDate - ( ValuesToShow * 7 ), DisSelectedDate, 7 )
VAR Result =
CALCULATE (
[Total Sales],
KEEPFILTERS ( TREATAS ( DatesToShow, Dates[Date] ) )
)
RETURN
Result
``````

For going back dynamically you can use What IF parameters:

5 Likes

Thanks @AntrikshSharma. Great solutionđź¤©. This solution solve my problem

1 Like

@HosseinVossoughi

@Harsh Told me that upon selecting 7 or 6 the result was always the same number of days and then I realized for March 17, 2007 there is no sales, so if you want to deal with that situation you can use this to show 0 on such days

``````Hashem =
VAR ValuesToShow =  [Dates To Show Value]
VAR DisSelectedDate =
SELECTEDVALUE ( DisDate[Date] )
VAR DatesToShow =
GENERATESERIES ( DisSelectedDate - ( ValuesToShow * 7 ), DisSelectedDate, 7 )
VAR Result =
SUMX (
FILTER (
TREATAS ( DatesToShow, Dates[Date] ),
Dates[Date] IN VALUES ( Dates[Date] )
),
[Total Sales] + 0
)
RETURN
Result
``````

3 Likes

2 Likes

Tags - TREATAS, KEEPFILTERS, GENERATESERIES, Virtual Relationships, Go back N days, Travel back in time, advanced dax, disconnected table

1 Like

Fantastic video - you do a great job explaining some of the most difficult DAX concepts (e.g., TREATAS, data lineage) in really clear, easy to understand language. Plus this has perhaps the greatest title of any YouTube video weâ€™ve ever postedâ€¦

Thank you for the obvious time and effort you put into this video. I think everyone at an intermediate DAX level or above will benefit from watching it.

• Brian
2 Likes

@BrianJ Thanks!! I wanted to dress up like Avengers when they go for time travel but unfortunately my city is under lockdownâ€¦

1 Like

That just gives us something more to look forward to in your next videoâ€¦

• Brian
1 Like

amazing video!!!

1 Like