Matrix visualizations and dynamically expand the last month, last week of the last month

[razieh12]

Hello. 
 I created matrix in power bi, the rows are months, weeks, and days. and the value is %sale.

My goal is to enable expansion solely for the most recent month. From there, I desire only the last week of that month to be expandable, while keeping all other periods unexpanded. Importantly, I'm aiming for this behavior to be dynamic—no matter the updates to data or dates, the expansion setup should remain consistent as I've outlined

[Anonymous · Accepted solution]

Hi @razieh12,

I'd like to suggest you create a matrix with complete date range and Dax expression calculation result. You can create a measure formula to check the current date value and return flag.

After these steps, you can use this as condition to use on ‘visual level filter’ to filter records based on flag.

Sample formulas:

lastMonth flag =
VAR currDate =
    MAX ( Table1[Date] )
VAR _sysDate =
    TODAY ()
RETURN
    IF ( currDate < DATE ( YEAR ( _sysDate ), MONTH ( _sysDate ), 1 ), "Y", "N" )
    
lastweek flag =
VAR currDate =
    MAX ( Table1[Date] )
VAR _sysDate =
    TODAY ()
VAR weekdayOffset =
    WEEKDAY ( _sysDate )
RETURN
    IF (
        currDate
            <= DATE ( YEAR ( _sysDate ), MONTH ( _sysDate ), DAY ( _sysDate ) - weekdayOffset ),
        "Y",
        "N"
    )

Applying a measure filter in Power BI - SQLBI

Regards,

Xiaoxin Sheng