Making Table Visual that is Dynamic to New Parameter option chosen

[superhayan]

Hello all,

My raw data is a list of transaction record with date, time, product, $ values etc info. The data will be refreshed daily and I have used the New Parameter function to make a slicer to set the period for the cumulative total calculation of $ values to be 3/6/9/12 months dynamatically as below.

My question is: I will need to add a table visual into this dashboard page to show all the transaction records that are involved in this period - which I just basically need to show the transaction records without any measures needed. In this way, when I set to 12 months, the table will show all the transaction records that are done during 12/5/2023 - 11/5/2024; and when I choose 6 months, the table will show all transaction records that are done during 12/11/2023 - 11/5/2024. (ddmmyyyy format)

I tried to use the new parameter to add a new column in the raw data table to indicate whether each transaction record is "In-Period" such that I can limit the table visual to show only "In-Period" records. However it doesn't work because raw data table can only show static data. Please can I know how can I create a record table visual that is dynamic to the new parameter I choose?

Sorry that I cannot share any data file because I cannot access to any cloud drive with my work laptop. Thanks in advance!

[Ashish_Mathur]

Hi,

I have solved a similar problem in the attached files.

Hope this helps.

[v-zhengdxu-msft]

Hi @superhayan 

Here I create a set of sample:

Then create a new table:

Table 2 = {3,6,9,12}

Add measures:

Start = 
VAR _slicer = MAX('Table 2'[Value])
RETURN DATE(YEAR(TODAY()),MONTH(TODAY())-_slicer,DAY(TODAY()))

End = TODAY()

_Sales =
IF (
    MAX ( 'Table'[Date] ) >= [Start]
        && MAX ( 'Table'[Date] ) <= [End],
    SUM ( 'Table'[Sales] )
)

The result is as follow:

Here is the pbix for your reference.

Best Regards

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

[superhayan]

Thanks for your reply! Your solution can almost get what I want to acheive. Just that I don't need to calculate the sum of sales in the table. For example, if there are 2 sales on 2024-02-13 with Sales $101 and $20, then in the table visual, when I choose 3 months period, there will be 2 records of 2024-02-13 of $101 and $20 respectively, intead of 1 summarized record of $121.

Could you guide me how I can change the dax to achieve this? Thank you!

[v-zhengdxu-msft · Accepted solution]

Hi @superhayan 

Unfortunately that this is almost unachievable, but maybe you can consider the alternative workaround:

Here I change the data, two 3.15.2024

Then change the measure:

_Sales =
VAR _currentDate =
    MAX ( 'Table'[Date] )
RETURN
    IF (
        MAX ( 'Table'[Date] ) >= [Start]
            && MAX ( 'Table'[Date] ) <= [End],
        CALCULATE (
            SUM ( 'Table'[Sales] ),
            FILTER ( ALLSELECTED ( 'Table' ), 'Table'[Date] = _currentDate )
        )
    )

Then create a matrix like this:

You can alse use the conditional formatting to highlight the data wihch have more than one date:

Color =
VAR _currentDate =
    MAX ( 'Table'[Date] )
RETURN
    IF (
        CALCULATE (
            COUNTROWS ( 'Table' ),
            FILTER ( ALLSELECTED ( 'Table' ), 'Table'[Date] = _currentDate )
        ) > 1,
        "Red"
    )

The result is as follow:

Best Regards

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