Dax Calculation for choose N specific date

DAX DAX Calculations
1

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

2

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
3

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

4

@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

image
image678Ă—530 27.7 KB

For going back dynamically you can use What IF parameters:

image
image774Ă—551 34 KB

5 Likes
5

Thanks @AntrikshSharma. Great solution🤩. This solution solve my problem

1 Like
6

@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

image
image808Ă—534 39.9 KB

3 Likes
7

this

2 Likes
8

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

1 Like
9

@AntrikshSharma ,

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
10

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

1 Like
11

@AntrikshSharma ,

That just gives us something more to look forward to in your next video…

  • Brian
1 Like
12

amazing video!!! :open_mouth:

1 Like