Calculate using multiple filters

[aredmond127]

Hi all,

I'm trying to create a measure where 'Current CDP - Total' column is summed with the following conditions:

- If OOS column is blank, sum the whole column regardless of month 

- If OOS column is not blank, sum the whole column but exclude next 3 months i.e. where N3M is not blank

I'm trying to use this N3M column because I don't want the date to be dynamic by using today() function. I've been trying to use calculate but getting stuck. For context, SKU level is the lowest granularity but the measure needs to also apply at an aggregated level i.e. for region.

Hope this makes sense and someone can kindly explain how to do this. 

Data Tables - M Dates for N3M

Raw data for Current CDP

[speedramps]

Try this

Totalvalue =

CALCULATE(
SUM(yourtable[yourvalue]),

ALLEXPEPT(yourtable, yourtable[Region],yourtable[Sku]
) 

Totalvalue3m =

CALCULATE(
SUM(yourtable[yourvalue]),

ALLEXPEPT(yourtable, yourtable[Region],yourtable[Sku],
ISBLANK(yourtable[N3M])y

) 

Your answer =
IF (
ISBLANK(   SELECT(yourtable[OOS])  ),
Totalvalue3M,
Totalvalue) 

Please cleick thumbs up and accept as solution. Thanks 😀

[aredmond127]

Hi, 

thanks for this however it is not quite working - rather than removing current CDP values for only N3M, it is not populating it at all. Please see screenshot below.

Note I did not use the all except part - apologies I didn't make it clear in the original post. I want users to be able to adjust granularity for all report measure i.e. region, brand, category, sub category etc. so I believe I don't need this part?