How to get 10 days - Dates as columns in Matrix value based on slicer selection

[jayasurya_prud]

I am working on a task where I have created a matrix and slicer

matrix 

rows - category

columns - calendar[date] column

values - distinct count of key

now the problem i am trying to solve is 

based on the date selected in slicer, my matrix columns should display only the slicer seleced date and prior dates columns 

if I select 2/12/2023

 i need to see columns only from 2/2/2023 to 2/12/2023

how to get this?

I tried many ways creating calendar table

using measure in visual level filter by selecteddate, min and mox date, daterange, crossfilter, keepfilter, treatas, all, and many

but not getting to the solution

main problem is based on the date i select on slicer I only get one column (ex: If I select 2/12/2023, I only get one col). But i dont want that. Based on the date I select in slicer I need the prior days in matrix columns too. 

the most close approach I tried is to create a measure 

Distinct Visit Count =

VAR SelectedDate = SELECTEDVALUE('Table'[Date])

VAR MinDate = SelectedDate - 29

VAR MaxDate = SelectedDate

VAR IsInRange =

    MAX('Calendar'[Date]) >= MinDate &&

    MAX('Calendar'[Date]) <= MaxDate

VAR Calc =

        CALCULATE(

            DISTINCTCOUNT('table'[Key])

       , 'Table'[Date] >= MinDate &&

         'table'[Date] <= MaxDate

        )

RETURN

    IF(ISBLANK(Calc), 0, Calc)

and use this mesure in values of matrix

If i use this, I am getting all the date columns but vlues pooping only for the slicer date selected and remaining all columns as 0

how to get the solution

@amitchandak 

@hackcrr 

@v-kmc 

@v-cyu 

@v-jingzhang 

@v-jiewu-msft 

@v-jianpeng-msft 

@v-nuoc-msft 

and #superusers

[Ashish_Mathur · Accepted solution]

Hi,

I have solved a similar question in the attached file.

Hope this helps.

[Uzi2019 · Accepted solution]

Hi @jayasurya_prud 

create Column then write this

AverageRate =
VAR currentDate = 'Table1'[Date]
VAR startDate = currentDate - 9
RETURN
    IF (
        MIN ( 'Table1'[Date] ) > startDate,
        BLANK (),
        CALCULATE (
            DISTINCTCOUNT ( Table1[Rate] ),
            FILTER (
                ALL ( 'Table1' ),
                'Table1'[Date] <= currentDate
                    && 'Table1'[Date] >= startDate
            )
        )
    )

I hope I answered your question! 

[Uzi2019]

Hi @jayasurya_prud 

you can use same logic of this video instead of month you can change it to 10 days.

https://www.youtube.com/watch?v=duMSovyosXE&t=76s

I hope I answered your question!

[Ashish_Mathur · Accepted solution]

Hi,

I have solved a similar question in the attached file.

Hope this helps.