Forum Discussion
Date manipulation
Hello everyone.
I need help to create a measure that will allow me to do next:
1. If I click on 2022 year, only data for last month will show up.
2. If I click on the H1, Q1-2022, or on separate month then data shows normally for a chosen time period.
Thank you for any help!
Hi Anonymous
You need to create measures for values in the matrix visual to reflect this kind of filter. For example, you can try below measure for Account (#) (I assume that it's a simple COUNT calculation).
Account (#) = VAR _lastDate = MAXX ( ALLSELECTED ( 'Table' ), 'Table'[Date] ) VAR _lastMonth = YEAR ( _lastDate ) * 100 + MONTH ( _lastDate ) RETURN IF ( HASONEVALUE ( 'Date'[H] ), COUNT ( 'Table'[Account] ), CALCULATE ( COUNT ( 'Table'[Account] ), ALLSELECTED ( 'Date' ), 'Date'[YearMonth] = _lastMonth ) )For above measure, I assume that there is a Date column in fact table 'Table' which has a "Last Date", and that there is a "202201" format "YearMonth" column in Date table. The YearMonth column is of Number data type.
If this doesn't help, please share some dummy data to show the tables you have. This can help me try to provide a more accurate solution as my assumptions of your tables may not match your tables.
Best Regards,
Community Support Team _ Jing
If this post helps, please Accept it as Solution to help other members find it.
2 Replies
Hi Anonymous
You need to create measures for values in the matrix visual to reflect this kind of filter. For example, you can try below measure for Account (#) (I assume that it's a simple COUNT calculation).
Account (#) = VAR _lastDate = MAXX ( ALLSELECTED ( 'Table' ), 'Table'[Date] ) VAR _lastMonth = YEAR ( _lastDate ) * 100 + MONTH ( _lastDate ) RETURN IF ( HASONEVALUE ( 'Date'[H] ), COUNT ( 'Table'[Account] ), CALCULATE ( COUNT ( 'Table'[Account] ), ALLSELECTED ( 'Date' ), 'Date'[YearMonth] = _lastMonth ) )For above measure, I assume that there is a Date column in fact table 'Table' which has a "Last Date", and that there is a "202201" format "YearMonth" column in Date table. The YearMonth column is of Number data type.
If this doesn't help, please share some dummy data to show the tables you have. This can help me try to provide a more accurate solution as my assumptions of your tables may not match your tables.
Best Regards,
Community Support Team _ Jing
If this post helps, please Accept it as Solution to help other members find it.