Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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 :
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
Solved! Go to 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
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.
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
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.
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.
Thanks @AnkitBI and @v-juanli-msft ,
Both solutions are same and worked for me with small changes.
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.
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?
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
106 | |
94 | |
77 | |
65 | |
53 |
User | Count |
---|---|
145 | |
105 | |
104 | |
90 | |
63 |