latest date in the slicer

[Anonymous]

Hi All,

I need your help in this. I am trying to set the date slicer value to the latest available date. I followed the post https://community.powerbi.com/t5/Desktop/Filter-Dates-to-only-show-current-month-and-next-12-months/...

Only difference between this solution and my issue is, the data is not available for the current running month. For example current month is Jan 2020 but the data available is for 30 Sep 2019. So i have to modify the DAX . Because i have future dates in Dim_Date dimension and in the Sales fact, last sale is in Sep 2019, i have created one measure to find date for available data

Measure : 

Slicer max date = CALCULATE(MAX(Dim_Date[Date]),RELATEDTABLE('SalesFact'))

and then using this measure in below column calculation

Slicer Date column =(IF('DIM_Date'[Date]= [Slicer max date],"Latest Date",'Dim_Date'[Date]&""))

and then using this column for the slicer.

Issue is : it just give one value in the slicer as "Latest Date", which gives all the dates on or before 30 Sep 2019 as "Latest Dates  and also shows dates after 30 Sep 2019.

Please let me know how can we fix this

[AnkitBI · Accepted solution]

Hi   

You can try adding below calculated columns.

1) To Mark maximum date in Sales Table as Latest Date.

Slicer Date column = if('date table'[Date] = max(Data[Date]),"Latest Date",'date table'[Date]&"")

2) To identify Dates before/After sales date.

Is Active Dates = if('date table'[Date] <= max(Data[Date]),1,0)

Use Is Active Dates in Slicer Filter to limit till your Max Sales Date. 

Thanks
Ankit Jain
Do Mark it as solution if the response resolved your problem. Do Kudo the response if it seems good and helpful.

@Anonymous

[AnkitBI · Accepted solution]

Hi   

You can try adding below calculated columns.

1) To Mark maximum date in Sales Table as Latest Date.

Slicer Date column = if('date table'[Date] = max(Data[Date]),"Latest Date",'date table'[Date]&"")

2) To identify Dates before/After sales date.

Is Active Dates = if('date table'[Date] <= max(Data[Date]),1,0)

Use Is Active Dates in Slicer Filter to limit till your Max Sales Date. 

Thanks
Ankit Jain
Do Mark it as solution if the response resolved your problem. Do Kudo the response if it seems good and helpful.

@Anonymous

[Anonymous]

Hi @AnkitBI  

Thanks for your reply.  Actually the requirement is to show Latest date along with other dates in the dropdown slicer. when "latest date" is selected, we should see the data for latest available date. USer should be able to select any other date as well.

For example: if the last sale was on 17 dec 2019, then this date becomes the "latest date" in the slicer and all dates before this will also be shown 

thanks.

[AnkitBI]

Hi @Anonymous  

Have you tried the solution I provided. It will give exactly what you want. @v-juanli-msft had adeed same with screenshots. You can check that else let me know and I will share the sample file.

Thanks
Ankit Jain
Do Mark it as solution if the response resolved your problem. Do Kudo the response if it seems good and helpful.

[Anonymous]

Thanks @AnkitBI and @v-juanli-msft ,

Both solutions are same and worked for me with small changes.

[v-juanli-msft · Accepted solution]

Hi @Anonymous 

You can use [Date] column or [Column 2] in the slicer,

Calendar date table, date table has a relationship with "FactSale" table based on [Date] column

date = CALENDARAUTO()

Create columns in Calendar table

Column = IF([Date]<=MAX(SaleFact[date]),1,0)

Column 2 = IF([Date]<MAX(SaleFact[date]),FORMAT([Date],"m/d/yyyy"),IF([Date]=MAX(SaleFact[date]),"Last Date",BLANK()))

You can use [Column 2] in a slicer directly, or use [Date] in the slicer and add [Column] to the page level filter.

Best Regards
Maggie
Community Support Team _ Maggie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[GChristensen]

In Column 2 you have "Last date" shown but the other dates are out of chronological order. Is there a way to fix the other dates so they are in order?