Forum Discussion
DAX Measure based on selected filter value
Hi Everyone!
Just an FYI - I’m very new at using DAX.
I’m wanting to build a WIP report for Month end to calculate unrecognised revenue. I’m wanting to create a measure and link a date filter which’ll calculate the unrecognised revenue value as per the selected filter date. The aim is for users to select their desired date.
The formula I’d like to achieve:
SUM([Revenue]) WHERE [P&L date] > “[Selected Filtered Date]” + SUM([Revenue]) WHERE [P&L Date] IS NULL.
With the above grouped by Reporting Month.
Format of my data:
| Reporting Month | P&L Date | Revenue |
| 31/01/2023 | 31/01/2023 | 100 |
| 31/01/2023 | 28/02/2023 | 100 |
| 31/01/2023 | NULL | 150 |
| 31/01/2023 | 31/03/2023 | 100 |
| 28/02/2023 | 28/02/2023 | 200 |
| 28/02/2023 | NULL | 100 |
| 28/02/2023 | 30/04/2023 | 250 |
| 31/03/2023 | 31/03/2023 | 150 |
| 31/03/2023 | 30/04/2023 | 300 |
| 31/03/2023 | NULL | 100 |
So by having a filter select the date “28/02/23” the measure will calculate the unrecognised revenue as £250 for Reporting Month “31/01/23”
Any help/suggestions would be greatly appreciated.
Happy to give more detail if needed
Thanks!
Hi Anonymous ,
Here are the steps you can follow：
1. Create measure.
Measure = var _select=SELECTEDVALUE('Table'[Reporting Month]) return SUMX( FILTER(ALL('Table'), 'Table'[Reporting Month]<>_select&&'Table'[P&L Date]=BLANK()),[Revenue])2. Result:
Best Regards,
Liu Yang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly
1 Reply
Hi Anonymous ,
Here are the steps you can follow：
1. Create measure.
Measure = var _select=SELECTEDVALUE('Table'[Reporting Month]) return SUMX( FILTER(ALL('Table'), 'Table'[Reporting Month]<>_select&&'Table'[P&L Date]=BLANK()),[Revenue])2. Result:
Best Regards,
Liu Yang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly