Power BI Relative Date Slicer

Question

I need to create a dynamic table that filters certain records from the original transactional table based on a pre-defined date selection. The transactional table (vwProcurementDashboard) is structured like:

import_date	group	quantity	value
2021-01-01	A	100	10
2021-01-01	B	100	15
2021-01-02	A	110	10
2021-01-02	B	110	15

Now, I want to filter records using slicer (I) last one month, (II) last 3 months, etc. for which I have created one configuration table (cfg_date_group_frequency) as below:

I've created the following additional measures to filter the data as below:

dynMonthValue =
    // get the number value that needs to be subtracted to create a data filter
    SWITCH(
        TRUE(),
        SELECTEDVALUE(cfg_date_group_frequency[DateGroupName]) == "Last 1 Month",   0,
        SELECTEDVALUE(cfg_date_group_frequency[DateGroupName]) == "Last 3 Months",  2,
        SELECTEDVALUE(cfg_date_group_frequency[DateGroupName]) == "Last 6 Months",  5,
        SELECTEDVALUE(cfg_date_group_frequency[DateGroupName]) == "Last 12 Months", 11,
        12 // this is the default number of data that should be returned
    )


avlMaxDate =
    // get the latest available date from the vwAnalyticsDashboard
    MAX(vwProcuementDashboard[import_date])


dynFromDate = 
    // calculate the date from which the data needs to be filtered
    VAR SelectedMonth = MONTH([avlMaxDate]) - [dynMonthValue]
    VAR SelectedYear = YEAR([avlMaxDate]) - IF(SelectedMonth < 1, 1, 0)
    RETURN
        DATE(SelectedYear, IF(SelectedMonth < 1, 12 + SelectedMonth, SelectedMonth), 1)


dynFiltProcurementDashboard = 
    // dynamically fetch only the selected times from the table
    RETURN
        FILTER(
            vwProcuementDashboard,
            vwProcuementDashboard[import_date] >= [dynFromDate]
        )

However, when I check the data MIN(dynFiltProcurementDashboard[import_date]) the records are not filtered. However, I have checked separately that all the other values are correctly populated.

As far as I have seen, this is a very standard and straightforward approach, but I am not sure what I am doing wrong. My questions:

1. Why is the table not filtered?
2. Is there a better way to achieve this?

EDIT: Please find a sample masked .pbix file: https://github.com/ZenithClown/SO-77125934

Answer 1

I think I understand. If you're asking why your calculated table is not reacting to slicers, then it is because calculated tables are evaluated once only at refresh time. They won't respond to slicers or anything other than what you put in the table definition.

Answer 2

It is hard to diagnose without a sample .pbix but it seems there are simpler ways to achieve you goals. Why not just use a date slicer with Relative Date option selected?