Dates based on Slicer selection

[Jyaul1122]

Hi,

I have two tables Fact and Date table, both are link many to one with Fact(data_date) and Date table(Dates).

Fact Table:

Project	Data_Date	Actual Date
Project1	5/22/2025	11/19/2030
Project1	5/29/2025	12/18/2030
Project1	6/5/2025	1/16/2031
Project1	6/12/2025	2/14/2031
Project2	5/22/2025	3/1/2031
Project2	5/29/2025	3/16/2031
Project2	6/5/2025	3/31/2031
Project2	6/12/2025	4/15/2031
Project3	6/19/2025	5/5/2032
Project3	6/26/2025	5/6/2032

Dates Table:

Dates

5/22/2025

5/29/2025

6/5/2025

6/12/2025

6/19/2025

6/26/2025

7/3/2040

7/5/2042

7/6/2042

7/7/2042

7/8/2042

I have slicer from Fact table(Data_date)-single select, I would like to display Actual Date  from Fact table where Fact(Data_Date)<=max(Data_Date) in matrix. Value(Actual Date) will be change based on Dates slicer.

For example if date selected=6/12/2025 then output will be upto data_date 6/12/2025:

		Dates(Date table)
Project	5/22/2025	5/29/2025	6/5/2025	6/12/2025
Project1	11/19/2030	12/18/2030	1/16/2031	2/14/2031
Project2	3/1/2031	3/16/2031	3/31/2031	4/15/2031

if date selected=6/19/2025 then output will be upto data_date 6/19/2025:

		Dates(Date table)
Project	5/22/2025	5/29/2025	6/5/2025	6/12/2025	6/19/2025
Project1	11/19/2030	12/18/2030	1/16/2031	2/14/2031
Project2	3/1/2031	3/16/2031	3/31/2031	4/15/2031
Project3					5/5/2032

, how can i achive using DAX ?

[v-sshirivolu · Accepted solution]

Hi @Jyaul1122 ,
Thank you for reaching out to Microsoft Fabric community.

I got this working and thought I’d share how I did it,
I generated a separate slicer table from Fact[Data_Date] by creating a calculated table as shown below:
Slicer_Date = DISTINCT(Fact[Data_Date])

There’s no relationship between this table and any other — it’s just used for the slicer.

Then I created a measure to check if each date in the matrix should be shown, based on the slicer selection:

Show Column = VAR SelectedDate = SELECTEDVALUE(Slicer_Date[Data_Date])
VAR ThisColumnDate = SELECTEDVALUE(Dates[Dates])
RETURN
IF (
    NOT ISBLANK(SelectedDate) &&
    NOT ISBLANK(ThisColumnDate) &&
    ThisColumnDate,
    1,
    0
     )

And finally, this is the measure I used to get the actual date values in the matrix:

Selected Actual Date =
VAR SelectedDate = SELECTEDVALUE(Slicer_Date[Data_Date])
VAR ThisDate = SELECTEDVALUE(Dates[Dates])
VAR ThisProject = SELECTEDVALUE(Fact[Project])

RETURN
IF (
    NOT ISBLANK(SelectedDate) &&
    NOT ISBLANK(ThisDate) &&
    NOT ISBLANK(ThisProject) &&
    ThisDate <= SelectedDate,
    CALCULATE (
        MAX(Fact[Actual Date]),
        FILTER (
            Fact,
            Fact[Project] = ThisProject &&
            Fact[Data_Date] = ThisDate
        )
    )
)

In the matrix, I placed Dates[Dates] on the rows, Fact[Project] on the columns, and used the Selected Actual Date measure as the value. I also applied Show Column as a visual-level filter, setting it to 1 so that only columns on or before the selected date are displayed.

The slicer uses the Slicer_Date table we set up initially.

Everything is functioning as intended the matrix updates according to the selected date and displays only the relevant values up to that point.

Please find the attached .pbix file for your reference

Regards,
Sreeteja.

[v-sshirivolu]

Hi @Jyaul1122 ,
I hope the information provided above assists you in resolving the issue. If you have any additional questions or concerns, please do not hesitate to contact us. We are here to support you and will be happy to help with any further assistance you may need.

[v-sshirivolu]

Hi @Jyaul1122 ,

I hope the above details help you fix the issue. If you still have any questions or need more help, feel free to reach out. We’re always here to support you

[v-sshirivolu]

Hi @Jyaul1122 ,
I wanted to check if you had the opportunity to review the information provided. Please feel free to contact us if you have any further questions

[Ashish_Mathur]

Hi,

PBI file attached.

Hope this helps.

[Jyaul1122]

@Ashish_Mathur 

Thanks for the reply, but I have slicer from the Fact table[Data_Date], Please do needful .Thanks

[Ashish_Mathur]

You are welcome.  That is not a good practise.  You must always create a Calendar Table.

[FBergamaschi]

This is a DAX question, pls post it again here

https://community.fabric.microsoft.com/t5/DAX-Commands-and-Tips/bd-p/DAXCommands

[FBergamaschi]

Hoping you repost it in the right place, here is my solution (please before accepting it as a solution in case, repost cancel the post from here and repost it in the right secttion, I shall reanswer there)

In the above slicer you have the date column form your date table (that I strongly suggest you create in a complete fashion, you should never have a date table with incomplete years, you only see the dates in which there a rows in the fact table as I injected a filter in the slicer visual for the measure 

Nr Rows = CONTROWS (Fact) and forced it to be non blank

In the above table visual you have the columns from your fact table and the following measure in DAX

Selection =

VAR MaxDate = SELECTEDVALUE( 'Date'[Date] )

VAR FactDateUpTOMaxDate =

CALCULATETABLE(

    'Fact',

    REMOVEFILTERS( 'Date' ),

    'Fact'[Data_Date] <= MaxDate

)

RETURN

    COUNTROWS(

    FactDateUpTOMaxDate)

If this helped, please consider giving kudos and mark as a solution

@me in replies or I'll lose your thread

consider voting this Power BI idea

Francesco Bergamaschi

MBA, M.Eng, M.Econ, Professor of BI

[Jyaul1122]

@FBergamaschi  thanks for your reply but my expecated result will be like in Matrix:
 if date selected=6/12/2025 then output will be upto data_date 6/12/2025:

if date selected=6/19/2025 then output will be upto data_date 6/19/2025:

Column in Matrix coming from Dates table.

Could you please do needful ?

[FBergamaschi]

I do not understand, what is this forst row in color? Maybe you need to explain more in detail what you are looking for. In my result, I see dates up to the selected one as you asked, now I do not understand the tables you show, it seems an excel table in which you select in a single cell what you want to see, this is impossible in Power BI

best