Forum Discussion

elmurat's avatar
elmurat
Icon for Helper II rankHelper II
1 year ago
Solved

When I select a date, also show previous 3 dates.

Hello,   Please see the attached pbix for dummy data. I need to create a dashboard, where the user selects one date from the date slicer (let's say 11/25/2024), and the table visual will show thes...
Tips and Tricks
  • Anonymous's avatar
    Anonymous
    1 year ago

    Hi elmurat ,

     

    According to your description, you could try this method.

    1. Generate a calendar table based on the maximum and minimum dates of the Date column.

    CalendarTable = 
VAR MinDate = MINX(ALL('Table'),'Table'[Date])
VAR MaxDate = MAXX(ALL('Table'),'Table'[Date])
RETURN
ADDCOLUMNS(
    CALENDAR(MinDate, MaxDate),
    "Year", YEAR([Date]),
    "Month", MONTH([Date]),
    "Day", DAY([Date]),
    "Quarter", QUARTER([Date]),
    "Weekday", WEEKDAY([Date])
)

    2. Create a DAX measure to generate a virtual table with an interval of seven days, and determine whether the date of the date column is in the virtual table.

    Tag = 
VAR SelectedDate = SELECTEDVALUE('CalendarTable'[Date]) 
VAR DateTable =
ADDCOLUMNS(
    GENERATESERIES(0, 3, 1),
    "Date", SelectedDate - [Value] * 7
)
RETURN IF(ISBLANK(SelectedDate),1,if(CONTAINS(DateTable,[Date],MAX('Table'[Date])),1,0))

    3. Set Filters.

     

     

     

     

     

    Best regards,

    Mengmeng Li

Bibiano_Geraldo's avatar
Bibiano_Geraldo
Icon for Super User rankSuper User
1 year ago

Hi, 

Please share no sensitive sample file

elmurat's avatar
elmurat
Icon for Helper II rankHelper II
1 year ago

Hi Bibiano, here is the link to the test pbix file:  TestPBIX.pbix. Let me know if you can't access the file.

 

  • Bibiano_Geraldo's avatar
    Bibiano_Geraldo
    Icon for Super User rankSuper User
    1 year ago

    Hi elmurat ,

    To achieve desired result asked in the file, please create the following measure:

    For Diference:

    Diference = 
VAR totalWeek = 
    CALCULATE(
        SUM(Weekly[Count]),
        FILTER(
            Weekly,
            Weekly[4 Weeks Filter] = 1
        )
    )

VAR vDate = SELECTEDVALUE(Calendar_Table[Date]) - 28

VAR totalMonth = 
    CALCULATE(
        SUM(Monthly[Count]),
        FILTER(
            Monthly,
            Monthly[Date] = vDate
        )
    )

RETURN
    totalWeek - totalMonth


    For Diference %

    Diference % = 
VAR totalWeek = 
    CALCULATE(
        AVERAGE(Weekly[Percent]),
        FILTER(
            Weekly,
            Weekly[4 Weeks Filter] = 1
        )
    )

VAR vDate = SELECTEDVALUE(Calendar_Table[Date]) - 28

VAR totalMonth = 
    CALCULATE(
        AVERAGE(Monthly[Percent]),
        FILTER(
            Monthly,
            Monthly[Date] = vDate
        )
    )

RETURN
    totalWeek - totalMonth

     

    Your output will look like this: