DAX Measure based on selected filter value

[Anonymous]

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!

[Anonymous · Accepted solution]

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