How to create a table to use as a filter from two dynamic measure.

[Shivanshu]

Hi everyone,

Sorry, if this question is posted before but I didn't find any solution that works for me. 

I have a list of valuation dates for historical, current and future data. I only show historical and current data in the dashboard. I have then a filter slicer for all charts that shows values for one single date. Now in one chart, I would like to show two data points for any valuation date and it's previous valuation date. If I edit interaction of this chart of historical and current data value shows. 

To solve this problem, I created 2 dynamic measures of selected date and the previous week date to the selected date. Is there any way that I can append this 2 measures to create a calculated table and then filter the visual?- or is there any other way that you can think of?

Related to this, I also had problem to get the previous week dynamically so I used -7 to the selected date. Is there any way I can use the filter context to get the selected date, previous week date to selected date and then get the table to filter the visual. 

For example: Filter is 03.06.2024

Then selected date = 03.06.2024

Previous week date = 27.05.2024

If it changed to Filter = 27.05.2024 

Then selected date = 27.05.2024

Previous week date = 20.05.2024

And the chart should only show 2 valuation dates at a time.

Help is much appreciated!

Many Thanks!

Shivanshu

[Anonymous · Accepted solution]

Hi @Shivanshu 

For your question, here is the method I provided:

Here's some dummy data

“Table”

First, you need a virtual date table to create a slicer.

DATE = SELECTCOLUMNS('Table', "DATE", 'Table'[DATE])

Create a measure.

Measure DATE = 
VAR _DATE = MAX('DATE'[Date])
VAR _DIFFDATE = _DATE - 7
RETURN
CALCULATE(
    SELECTEDVALUE('Table'[DATE]), 
    FILTER(
        'Table', 
        'Table'[DATE] = _DATE || 'Table'[DATE] = _DIFFDATE
    )
)

Or the other way around.

Measure DATE = 
VAR _DATE = MAX('DATE'[Date])
VAR _DIFFDATE = _DATE - 7
RETURN
IF(
    SELECTEDVALUE('Table'[DATE]) = _DATE 
    ||
    SELECTEDVALUE('Table'[DATE]) = _DIFFDATE, 
    1, 
    BLANK()
)

Regards,

Nono Chen

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[ryan_mayu]

could you pls provide some sample data and expected output?