Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago
Solved

Check for suddenly missing values

Hi everyone,   I'm trying to have a list of the SKUs that was selling for at least 3 consecutive months but they werent selling anything in thier fourth month.   I have made a measure to check fo...
Need Help
  • v-robertq-msft's avatar
    v-robertq-msft
    5 years ago

    Hi, Anonymous 

    According to your description, you can follow my steps:

    This is my test data:

     

    1. Create a calculated table [Missing SKUs In April]:
    Missing SKUs In April =

var _SKU=SUMMARIZE('Table',[SKU Number])

var _table=SELECTCOLUMNS(FILTER('Table',[Month]=4),"April SKU",[SKU Number])

var _Except=EXCEPT(_SKU,_table)

return _Except

     

    1. Create a calculated column in this calculated table:
    Flag =

var _count=

COUNTX(FILTER('Table','Table'[Month]>=1&&'Table'[Month]<=3&&'Table'[SKU Number]='Missing SKUs In April'[SKU Number]),'Table'[SKU Number])

return

IF(_count>=3,1,0)

     

    1. Create a table, place column[SKU Number] and set the visual filter(Flag is 1), like this:

     

    And you can get what you want.

    You can download my test pbix file here

     

    Best Regards,

    Community Support Team _Robert Qin

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

v-robertq-msft's avatar
v-robertq-msft
Community Support
5 years ago

Hi, Anonymous 

According to your description, you can follow my steps:

This is my test data:

 

  1. Create a calculated table [Missing SKUs In April]:
Missing SKUs In April =

var _SKU=SUMMARIZE('Table',[SKU Number])

var _table=SELECTCOLUMNS(FILTER('Table',[Month]=4),"April SKU",[SKU Number])

var _Except=EXCEPT(_SKU,_table)

return _Except

 

  1. Create a calculated column in this calculated table:
Flag =

var _count=

COUNTX(FILTER('Table','Table'[Month]>=1&&'Table'[Month]<=3&&'Table'[SKU Number]='Missing SKUs In April'[SKU Number]),'Table'[SKU Number])

return

IF(_count>=3,1,0)

 

  1. Create a table, place column[SKU Number] and set the visual filter(Flag is 1), like this:

 

And you can get what you want.

You can download my test pbix file here

 

Best Regards,

Community Support Team _Robert Qin

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.