Top 2 docs to be displayed

Anonymous 

Create a DAX Measure

Top 2 Docs = 
VAR SelectedMonth = MAX('Table'[Month]) 
VAR TopDocs = 
TOPN(
2, 
FILTER(
'Table', 
'Table'[Month] = SelectedMonth 
),
'Table'[Date], 
DESC
)
RETURN
COUNTROWS(TopDocs)

Place the Docname, Date, and Month fields in a Table visual.
Add the Top 2 Docs measure to the visual.

You can use the Filter pane to limit the visual to only the top 2 rows based on the Top 2 Docs measure, or
Add a Conditional Column to determine which rows to display based on the DAX logic.

💌 If this helped, a Kudos 👍 or Solution mark would be great! 🎉
Cheers,
Kedar
Connect on LinkedIn

Anonymous ,

Using a calendar table is considered a best practice for time intelligence calculations in Power BI, as it ensures consistency, simplifies DAX formulas, and enables better filtering. Here is how you can integrate a calendar table into your model and implement the solution to display the top two documents for the selected month.

First, create a calendar table in Power BI using DAX. This table should cover the range of dates in your dataset. You can write the following formula to create it:

Calendar = 
ADDCOLUMNS(
    CALENDAR(MIN('Table'[Date]), MAX('Table'[Date])),
    "Year", YEAR([Date]),
    "Month", FORMAT([Date], "YYYYMM"),
    "MonthName", FORMAT([Date], "MMMM"),
    "Day", DAY([Date])
)

Next, establish a relationship between the Date column in your table and the Date column in the newly created Calendar table. This ensures that the calendar table acts as the primary reference for time-based filtering. Use the Calendar table for slicers, such as the one for selecting a month.

Define a measure to rank documents by their date within the selected month. The DAX formula for this measure is:

Rank By Date = 
RANKX(
    FILTER(
        ALL('Table'),
        'Table'[Month] = MAX('Calendar'[Month])
    ),
    'Table'[Date],
    ,
    ASC,
    DENSE
)

Create another measure to filter only the top two documents based on their rank. The formula for this measure is:

Top 2 Docs = 
IF(
    [Rank By Date] <= 2,
    1,
    0
)

To visualize the data, add the columns Docname, Region, and Date from your table into a table visual. Add the Month column from the Calendar table to a slicer to enable the selection of the desired month. Apply a filter to the table visual to display rows where the measure Top 2 Docs equals one.

The use of a calendar table in this solution provides centralized time filtering, ensuring consistency across visuals and calculations. It also improves performance by clarifying the filter context in DAX formulas and allows for flexibility in time-based calculations, such as year-to-date or month-over-month comparisons.

For example, selecting the month 202408 in the slicer will display the following data:
Docname: ABC1241, Region: UK, Date: 20240807
Docname: ABC1242, Region: UK, Date: 20240808

Similarly, selecting the month 202409 will display:
Docname: ABC1238, Region: USA, Date: 20240901
Docname: ABC1239, Region: USA, Date: 20240902

This approach adheres to best practices by leveraging a calendar table, ensuring a robust and scalable reporting model.

Best regards,