Show dates for last 30 days in matrix visual depending on single selection of date slicer

[Mark-Yeap]

In Power BI, the date from date slicer ranges from 01-Oct-2023 to 22-Dec-2023. It can only select one date.

Whenever I select the date, I check the matrix visual that only the selected date is shown.

How do I set the matrix visual to show the last 30 days when I select the date from date slicer?

For example, when I select 22-Dec-2023 from date slicer, the dates from matrix visual are shown from 23-Nov-2023 to 22-Dec-2023.

When I select 21-Dec-2023 from date slicer, the dates from matrix visual are shown from 22-Nov-2023 to 21-Dec-2023.

What is the DAX? Is it new measure or new column?

[DallasBaba]

@Mark-Yeap Can you send the pbix file with a sample dataset ?

[DallasBaba]

@Mark-Yeap you can modify the DAX query to dynamically calculate the last 30 days based on the selected date from the date slicer, using the following :

Last 30 Days =
VAR SelectedDate = MAX('V_RPT_FTTH_SUBSCRIBER_BASE_DAILY'[BUSINESS_DATE])
VAR StartDate = SelectedDate - 30
VAR EndDate = SelectedDate
RETURN
CALCULATETABLE(
    SUMMARIZE(
        'V_RPT_FTTH_SUBSCRIBER_BASE_DAILY',
        'V_RPT_FTTH_SUBSCRIBER_BASE_DAILY'[BUSINESS_DATE],
        "Value", SUM('V_RPT_FTTH_SUBSCRIBER_BASE_DAILY'[TOTAL_GA])
    ),
    'V_RPT_FTTH_SUBSCRIBER_BASE_DAILY'[BUSINESS_DATE] >= StartDate &&
    'V_RPT_FTTH_SUBSCRIBER_BASE_DAILY'[BUSINESS_DATE] <= EndDate
)

I hope this meets your request. @ me in replies, or I'll lose your thread!!!  
If this post helps, please consider Accepting it as the solution to help others find it more quickly.  

Best Regards,

Dallas.

[Mark-Yeap]

Its okay. I have created a new table and copied some columns from old table into new table. One date column is showing the last 30 days depending on an extra date column.

E.g. In a new table, copied date column shows 23-Nov-2023 to 22-Dec-2023 when extra date column shows 22-Dec-2023.
When extra date column shows 21-Dec-2023, copied date column shows 22-Nov-2023 to 21-Dec-2023.

I also added the relationship between old and new table. It flows from old table to new table. I selected the date column from old table and an extra date column from new table.

As a result, when a date is selected from the date slicer, the table shows the last 30 days. Need to select columns from new table.

[DallasBaba]

@Mark-Yeap You can create a new measure using DAX. Here is the DAX formula that you can use:

Last 30 Days = 
VAR SelectedDate = MAX('Table'[Date])
RETURN
CALCULATETABLE(
    SUMMARIZE(
        'Table',
        'Table'[Date],
        "Value", SUM('Table'[Value])
    ),
    DATESBETWEEN(
        'Table'[Date],
        SelectedDate - 30,
        SelectedDate
    )
)

To answer your second question:

DAX stands for Data Analysis Expressions, serving as a formula language in Power BI for crafting custom calculations and aggregations in data analysis. DAX facilitates the creation of new columns or measures in a table.

In this instance, the above DAX formula creates a new measure called Last 30 Days that calculates the sum of the Value column for the last 30 days based on the selected date from the date slicer.

You can then add this measure to the matrix visual to show the last 30 days of data.

Let me know if this works for you. @ me in replies, or I'll lose your thread!!!  
Note: 

If this post helps, please consider Accepting it as the solution to help others find it more quickly.  

Best Regards,

Dallas.

[Mark-Yeap]

In Power BI, the date from date slicer ranges from 01-Oct-2023 to 22-Dec-2023. It can only select one date.
Whenever I select the date, I check the matrix visual that only the selected date is shown.

How do I set the matrix visual to show the last 30 days when I select the date from date slicer?

For example, when I select 22-Dec-2023 from date slicer, the dates from matrix visual are shown from 23-Nov-2023 to 22-Dec-2023.

When I select 21-Dec-2023 from date slicer, the dates from matrix visual are shown from 22-Nov-2023 to 21-Dec-2023.

I used DAX formula using new measure,

Last 30 Days =

VAR SelectedDate = MAX('V_RPT_FTTH_SUBSCRIBER_BASE_DAILY'[BUSINESS_DATE])

RETURN

CALCULATETABLE(

    SUMMARIZE(

        'V_RPT_FTTH_SUBSCRIBER_BASE_DAILY',

        'V_RPT_FTTH_SUBSCRIBER_BASE_DAILY'[BUSINESS_DATE],

        "Value", SUM('V_RPT_FTTH_SUBSCRIBER_BASE_DAILY'[TOTAL_GA])

    ),

    DATESBETWEEN(

        'V_RPT_FTTH_SUBSCRIBER_BASE_DAILY'[BUSINESS_DATE],

        SelectedDate - 30,

        SelectedDate

    )

)

However, it received an error message: The expression refers to multiple columns. Multiple columns cannot be converted to a scalar value.