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

razieh12 — Mon, 28 Aug 2023 17:22:41 GMT

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

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

Anonymous — Wed, 30 Aug 2023 02:16:40 GMT

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

topic Matrix visualizations and dynamically expand the last month, last week of the last month in DAX Commands and Tips

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

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