powerbidaxpowerbi-desktopdata-filtering

Power BI Relative Date Slicer


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:

cfg_date_group_frequency table

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.

power bi dashboard with dynamic selectedvalues

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/code-archived/SO-77125934


Solution

  • 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.