Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago
Solved

Allow indirect filter using allexcept

Hi Everyone,   Im trying to calculate the previous months sum of records but allow filtering on an attribulte (HomeRegion). I have a master table of HomeRegions joined to the enquiries table and wo...
Need Help
Tips and Tricks
  • richbenmintz's avatar
    richbenmintz
    5 years ago

    Hi Anonymous,

     

    This should do the trick

    CN_New Enquiries in past Month 2 = if(
max(DateMonthTable[YYYYMM])
>
format(
today(),
"yyyyMM"
),
0,
CALCULATE(
sum(CN_Enquiries[Number Of Enquiries]),
filter( 
    ALLEXCEPT(CN_Enquiries,'Teams'[Master HomeRegion]),
datediff( CN_Enquiries[EOM_EnquiryDate],
max(DateMonthTable[End of Month]),
MONTH)
= 1
)


))
richbenmintz's avatar
richbenmintz
Resident Rockstar
5 years ago

Hi Anonymous,

If you are able to provide a sample pbix file, I am sure parry2k or myself would be able to see your issue, pretty quickly.

 

Thanks

richbenmintz's avatar
richbenmintz
Resident Rockstar
5 years ago

Hi Anonymous,

 

This should do the trick

CN_New Enquiries in past Month 2 = if(
max(DateMonthTable[YYYYMM])
>
format(
today(),
"yyyyMM"
),
0,
CALCULATE(
sum(CN_Enquiries[Number Of Enquiries]),
filter( 
    ALLEXCEPT(CN_Enquiries,'Teams'[Master HomeRegion]),
datediff( CN_Enquiries[EOM_EnquiryDate],
max(DateMonthTable[End of Month]),
MONTH)
= 1
)


))
  • Anonymous's avatar
    Anonymous
    Not applicable
    5 years ago

    It did the trick. Thanks so much!

    • richbenmintz's avatar
      richbenmintz
      Resident Rockstar
      5 years ago

      Glad to Help