Help: stuggling with my calendar table to show "recent" by default

[eurenergy]

Hi all,

I have a calendar (dimension) table in my model, which I can use to slice and dice all the dates in the fact tables in my model. It concerns publish dates (so you can go back in time selecting a certain date).

I use the calendar table as a slicer in my report pages to filter the fact table data displayed in the visuals. However, I had multiple users who did not reset filters when using a report. So they frequently approach me telling the data is not up to date, while the date slicer simply still had a selection they made earlier.

I was tired of this, so I created a column in the calendar table (with data type text), which shows the dates but if it is the latest date in a fact table, it shows "recent". This way, I can set the slicer to "recent" by default and this solved the issue. All my colleagues are happy with it. 

However, now the moment of which I was afraid occurred: the fact tables don't have the same "recent" date anymore. 

So now it is not possible for me anymore to use the column of the dimension calendar table I created which shows "recent", as the "recent" date varies for each fact table. The best solution I can come up with is to start using the date column in the fact table, but that also does not really work as sometimes, I have data of multiple fact tables on a sheet. And I just want a single slicer to slice dates. 

I think this is a common problem for Power BI users, but still I can't think of a solution. What do you think? What would be a solution in which I can set the date slicer on "recent" and that it shows all the recent dates. But users must still be able to select previous dates as well. 

[v-echaithra]

Hi @eurenergy ,

We wanted to follow up to see if the issue you reported has been fully resolved. If you still have any concerns or need additional support, please don’t hesitate to let us know, we’re here to help.

We truly appreciate your patience and look forward to assisting you further if needed.

Warm regards,
Chaithra E.

[v-echaithra]

Hi @eurenergy ,

We wanted to follow up to see if the issue you reported has been fully resolved. If you still have any concerns or need additional support, please don’t hesitate to let us know, we’re here to help.

We truly appreciate your patience and look forward to assisting you further if needed.

Warm regards,
Chaithra E.

[v-echaithra]

Hi @eurenergy ,

We’d like to follow up regarding the recent concern. Kindly confirm whether the issue has been resolved, or if further assistance is still required. We are available to support you and are committed to helping you reach a resolution.

Thank you for your patience and look forward to hearing from you.
Best Regards,
Chaithra E.

[lbendlin]

Here is the general approach. Adjust as needed
- create a string representation of your date column, replace the latest date with a string "Latest"
- sort that new column by the original date column
- add a slicer or visual/page/report level filter
- set the filter to "Latest"
- publish the pbix to the workspace/app

[eurenergy]

Hi @lbendlin ,
this is what I did in the dim calendar table. But now, the fact tables all have different "latest" dates. So a latest field in the dim table does not work anymore.. 

[v-echaithra · Accepted solution]

Hi @eurenergy ,

Try this method by using a Disconnected Calendar Table for Slicer + DAX Logic

Create a Disconnected Calendar Table, so that you’ll have "Recent" as an option if the date matches any fact table's latest date.

Calendar_Slicer =
ADDCOLUMNS (
CALENDAR (MIN(Calendar[Date]), MAX(Calendar[Date])),
"DisplayDate",
VAR currDate = [Date]
VAR latestDate1 = CALCULATE(MAX(FactTable1[Date]))
VAR latestDate2 = CALCULATE(MAX(FactTable2[Date]))
RETURN IF (currDate IN { latestDate1, latestDate2 },"Recent",
FORMAT(currDate, "yyyy-MM-dd")))

Create a Measure That Applies Filtering Logic Based on the Slicer

SelectedDate = SELECTEDVALUE(Calendar_Slicer[Date])

FilterDate = IF (
SELECTEDVALUE(Calendar_Slicer[DisplayDate]) = "Recent", #return table with all recent dates from all fact tables
UNION (
SELECTCOLUMNS(FILTER(FactTable1, FactTable1[Date] = MAX(FactTable1[Date])), "Date", FactTable1[Date]),
SELECTCOLUMNS(FILTER(FactTable2, FactTable2[Date] = MAX(FactTable2[Date])), "Date", FactTable2[Date])
), #else, return selected date
SELECTCOLUMNS(
FILTER(Calendar, Calendar[Date] = SelectedDate),
"Date", Calendar[Date]))

Use this measure in your visual level filters or use TREATAS to inject this logic in your calculations.
SalesAmount = CALCULATE(
SUM(FactTable1[Amount]),
TREATAS (VALUES (Calendar_Slicer[Date]),
FactTable1[Date]))

Repeat similar logic for FactTable2 and others.

Hope this helps.
Best Regards,
Chaithra E.