Forum Discussion

cmkp2675's avatar
cmkp2675
Frequent Visitor
1 year ago
Solved

Page-Level Filtering based on the Latest Date

Dear Developers,          I'm a newbie to PBI, my requirement is "I want my Power BI Report/Dashboard visuals should show the "Latest Data" in every VISUALS on every refresh. I tried applying the Pa...
Need Help
Show and Tell
Tips and Tricks
Tutorial Requests
  • v-menakakota's avatar
    v-menakakota
    1 year ago

    Hi cmkp2675 ,

    Try this approach:
    Instead of filtering pages or visuals, control which data is shown through measures.

     

    If the user selects a date via slicer → that date is used.

     

    If the user does not select any date → the latest date is used automatically.

     

    Dont give any visual/page level filters instead use dynamic measure for this-

     

    EffectiveDate = 
    VAR SelectedDate = SELECTEDVALUE(MetricsData[Date])
    VAR LatestDate = CALCULATE(MAX(MetricsData[Date]), ALL(MetricsData))
    RETURN
    IF(ISBLANK(SelectedDate), LatestDate, SelectedDate)

     

    If you have Timeliness, Completeness, Validation on separate pages, just replicate the same logic for each dataset (change the table/column names accordingly).

    Below is the screenshot and pbix file for detailed information



    If this post was helpful, please give us Kudos and consider marking Accept as solution to assist other members in finding it more easily.

amitchandak's avatar
amitchandak
Super User
1 year ago

cmkp2675 , Hope you are using a dimension Table. Create New column in the date table

 

Latest = if ([Date] = Max(Table[Date]), 1, 0)

 

Use this as page level filter

 

If there are more than one table 

Latest = if ([Date] = Max( Max(Table[Date]),Max(Table1[Date]) ) 1, 0)

 

or

 

Latest = if ([Date] = MaxX( {Max(Table[Date]),Max(Table1[Date]), ,Max(Table2[Date])}, [Value] ) 1, 0)
Use this in the Page level filter  

 

very similar to Default Date Today/ This Month / This Year: https://www.youtube.com/watch?v=hfn05preQYA 

 

cmkp2675's avatar
cmkp2675
Frequent Visitor
1 year ago

Hi amitchandak 

Thank You for your response.

 

The issue is if we apply the visual or page level filter it's not responding to the external SLICER. In this case could you please go through the screenshot I attached? In data the there are multiple dates but once the visual level filter or Page level is applied, if we want to see the data of different dates it won't work. That's my biggest issue. Please help me to resolve.

LatestDate = CALCULATE(MAX(MetricsData[Date]), ALL(MetricsData))

Column DAX:
IsLatest = IF(MetricsData[Date] = [LatestDate], 1, 0) 




Thank You

Pradeep