Forum Discussion
Matrix Total with Date Filter
Hi all,
I have a matrix where the totals are not working correctly and need your expertise. What I am doing is for a time report. We have the user name as the row, the charge code where the time is charged as the column, and then the total hours as the values. But it is listing the total hours that have been entered since the start of time, and not just the past month.
Can anyone help with the DAX formula? Thanks
Is showing up as:
But for Jean should be:
Hi cedmiston ,
I suggest you use the following measure:
Measure = CALCULATE ( SUM ( 'Table'[Duration] ), FILTER ( 'Table', 'Table'[Date] >= DATE ( YEAR ( TODAY () ), MONTH ( TODAY () ) - 1, 1 ) && 'Table'[Date] < DATE ( YEAR ( TODAY () ), MONTH ( TODAY () ), 1 ) ) )If it doesn't meet your requirement, Could you please show the exact expected result based on the Tables that we have shared？
For more details, please refer to the sample pbix file: https://qiuyunus-my.sharepoint.com/:u:/g/personal/pbipro_qiuyunus_onmicrosoft_com/EU0LMfVjhtpDj7WQ42-s1ZUBsjzdx_N7nGJNq-zx5btPlg?e=Ka10mh
Best Regards,
Dedmon Dai
2 Replies
Hi cedmiston ,
I suggest you use the following measure:
Measure = CALCULATE ( SUM ( 'Table'[Duration] ), FILTER ( 'Table', 'Table'[Date] >= DATE ( YEAR ( TODAY () ), MONTH ( TODAY () ) - 1, 1 ) && 'Table'[Date] < DATE ( YEAR ( TODAY () ), MONTH ( TODAY () ), 1 ) ) )If it doesn't meet your requirement, Could you please show the exact expected result based on the Tables that we have shared？
For more details, please refer to the sample pbix file: https://qiuyunus-my.sharepoint.com/:u:/g/personal/pbipro_qiuyunus_onmicrosoft_com/EU0LMfVjhtpDj7WQ42-s1ZUBsjzdx_N7nGJNq-zx5btPlg?e=Ka10mh
Best Regards,
Dedmon Dai
Yes this worked thank you! Forgot to mark as solution.