Forum Discussion

aredmond127's avatar
aredmond127
Frequent Visitor
4 years ago

Calculate using multiple filters

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

 

 

 

 

 

Need Help

2 Replies

  • speedramps's avatar
    speedramps
    Super User
    4 years ago

    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's avatar
      aredmond127
      Frequent Visitor
      4 years ago

      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?