How to create a measure to filter those items sit in ware house for more then 3 year ?

[Anonymous]

Hi All

I have a inventory table , my Last Sold date field indicate when the unit sold last sold to customer. I need to write off those goods not sold more then 3 years from today. Can some one share with me how to write a measure to compute those items no sold for more then 3 year ?

Paul

[v-janeyg-msft]

Hi, @Anonymous 

It’s my pleasure to answer for you.

According to your description,I think you can create a measure, then use it in filter pane.

Like this:

over 3 years = IF(TODAY()-SELECTEDVALUE(INVC[Last Sold Date])>3*365,1,0)

If it doesn’t solve your problem, please feel free to ask me.

Best Regards

Janey Guo

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

[Anonymous]

Hi Janeyg

Thank you very much for your expression , it work fine. Can you share with me how to add one more condition , when last sold date is null ? so that below 2 items in red will dis-appear.

https://www.dropbox.com/s/qh1nu77k37b7gjr/PBT_V2021_89%20filter%20more%20then%203%20year%20not%20mov...

My pbi file :-

[v-janeyg-msft]

Hi, @Anonymous 

Try:

a =
IF (
    TODAY () - SELECTEDVALUE ( INVC[Last Sold Date] ) > 3 * 365
        || SELECTEDVALUE ( INVC[Last Sold Date] ) <> BLANK (),
    1,
    0
)

Best Regards

Janey Guo

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

[Anonymous]

Hi  Janey

I just check it give wrong result. Now it filter off less items . it should filter more items off.

https://www.dropbox.com/s/vzuz26hfbacetqs/PBT_V2021_89%20filter%20more%20then%203%20year%20not%20mov...

Paul

[v-janeyg-msft]

Hi, @Anonymous 

Try: 

a =
IF (
    TODAY () - SELECTEDVALUE ( INVC[Last Sold Date] ) > 3 * 365
        && SELECTEDVALUE ( INVC[Last Sold Date] ) <> BLANK (),
    1,
    0
)

Best Regards

Janey Guo

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

[Anonymous]

Hi Janey

Thank you very much , it work fine now. it is possible for you to help me add one more condition when On Han Amt = 0 don't flag.

Edit :-

Amit have advise to add filter for On Hand Amt >0 and it work fine,

Paul

[v-janeyg-msft · Accepted solution]

Hi, @Anonymous 

over 3 years =
IF (
    TODAY () - SELECTEDVALUE ( INVC[Last Sold Date] ) > 3 * 365
        && SELECTEDVALUE ( INVC[Last Sold Date] ) <> BLANK ()
        && SELECTEDVALUE ( INVC[OnHand Qty] ) <> 0,
    1,
    0
)

Best Regards

Janey Guo

[Anonymous]

Hi Janey

Thank you very much it work fine now. Can you help me take a look at below link , as now i need to plot bar chart for those slow moving items amount by BRAND_C

https://community.powerbi.com/t5/Desktop/How-to-plot-bar-chart-on-amount-by-Brand-C/m-p/1596956#M647...

Paul

[v-janeyg-msft]

Hi, @Anonymous 

Due to work policy, I can’t reply to other posts. If I have free time later, I will check it out.

Best Regards

Janey Guo

[amitchandak]

@Anonymous , Create a measure like this example and check

Rolling 36 = if(isblank(CALCULATE(sum(Sales[Sales Amount]),DATESINPERIOD('Date'[Date ],today(),-36,MONTH)) ),1, blank())

and view this by item

[Anonymous]

Hi Amit

thsnk you for sharing .

Can you pls help to create a measure base on Last Sold Date is more then 3 Year ? As I can not compute base on sales amount . Because this is inventory table. 

paul