Forum Discussion

obriaincian's avatar
obriaincian
Resolver I
4 years ago
Solved

Issue filtering dates power bi

Hi All,

 

 

I am relatively new to PBI and need some help.

 

I have a dataset which is similar to the below

Product             Expiry Date          Date          
Coke        02/02/2023           Jan-21       
Pepsi     23/08/2024          Jan-21         
7up      01/01/2023          Jan-21         
Fanta Jan-21
Coke       02/02/2023           Feb-21          
Pepsi        05/08/2024        Feb-21          
7up       01/01/2023          Feb-21           
Fanta02/04/2023Feb-21
Coke        08/05/2023         Mar-21           
Pepsi        23/08/2024         Mar-21          
7up        27/01/2023       Mar-21             
Fanta02/02/2023Mar-21   

 

What I want to do is to create a table or measure that groups each change and then filters the date column for the previous 2 months (in this case it would be Feb-21 and Mar-21) and then compare the corresponding Expiry Date rows for each product to see if the expiry date had changed and count the changes.

 

E.g output would be:

 

>1 Month Change              2
<1 Month Change      2
No Change      0
No Expiry Date

      0

 

 

 

 

 

Measure = 
var _minDate= CALCULATE(MIN('Table'[Date]),ALLEXCEPT('Table','Table'[Product]))
var _maxDate= CALCULATE(MAX('Table'[Date]),ALLEXCEPT('Table','Table'[Product]))
var _minED=  CALCULATE(MAX('Table'[Expiry Date]),FILTER( ALLEXCEPT('Table','Table'[Product]),'Table'[Date]=_minDate))
var _maxED=  CALCULATE(MAX('Table'[Expiry Date]),FILTER( ALLEXCEPT('Table','Table'[Product]),'Table'[Date]=_maxDate))
return SWITCH(TRUE(),
ISBLANK(_minED) || ISBLANK(_maxED), "No Expiry Date",
_maxED=_minED,"No change",
_maxED>EOMONTH(_minED,0),"> 1 month change",
_maxED<EOMONTH(_minED,0),"< 1 month change"
)

 

 

Need Help
  • lbendlin's avatar
    lbendlin
    4 years ago

    Here is my proposed solution. Note that I had to modify your test data to cover all scenarios.

     

    Value = 
var mm = max('Table'[Date])
var pm = edate(mm,-1)
var a = filter('Table','Table'[Date]=mm)
var b = ADDCOLUMNS(a,"prev expiry",var p = [Product] return CALCULATE(max('Table'[Expiry Date]),ALL('Table'),'Table'[Product]=p,'Table'[Date]=pm))
var c = ADDCOLUMNS(b,"diff",abs([Expiry Date]-[prev expiry]))
return SWITCH(SELECTEDVALUE(Buckets[Sort])
,"4",countrows(filter(c,ISBLANK([Expiry Date])))
,"3",countrows(filter(c,[diff]=0))
,"2",countrows(filter(c,[diff]<31 && [diff]<>0))
,"1",countrows(filter(c,[diff]>=31 && not ISBLANK([Expiry Date])))
)

    see attached

3 Replies

  • lbendlin's avatar
    lbendlin
    Super User
    4 years ago

    It is not clear what you are trying to measure and how you arrived at your desired result.  Here is a sample of your data and a graphical representation of your data.  Please indicate the expected outcome based on that table.

     

    let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45Wcs7PTlWAAiUdJQMjfSAyMjAyVkAAoLhXYp6ukSGMH6sTrRSQWlCcCZM2MtY3sABpM8GtC6rPvLQAYZuhPhCh2oZDm1tiXkkiUA4hDxZGcj4e17ulJiEbiO4BqGZTdD/g0gn3Ah4/oGtE9QXIoSZgTXCl6N4Bm22hD3QVitlAUd/EIixGo3sHS5Rg0YvmH7BGczQPYbMSi3+MYP6BK1aKjQUA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Product = _t, #"Expiry Date" = _t, Date = _t]),
    #"Changed Type with Locale" = Table.TransformColumnTypes(Source, {{"Expiry Date", type date}}, "en-GB"),
    #"Replaced Value" = Table.ReplaceValue(#"Changed Type with Locale","21","2021",Replacer.ReplaceText,{"Date"}),
    #"Changed Type" = Table.TransformColumnTypes(#"Replaced Value",{{"Date", type date}})
in
    #"Changed Type"

     

     

    • obriaincian's avatar
      obriaincian
      Resolver I
      4 years ago

      lbendlin thank you, the expected output is the table in my answer above. I want to show the difference in dates between the 2 previos months in the report date column. e.g I'd like to compare Feb-21 with Mar-21

      • lbendlin's avatar
        lbendlin
        Super User
        4 years ago

        Here is my proposed solution. Note that I had to modify your test data to cover all scenarios.

         

        Value = 
var mm = max('Table'[Date])
var pm = edate(mm,-1)
var a = filter('Table','Table'[Date]=mm)
var b = ADDCOLUMNS(a,"prev expiry",var p = [Product] return CALCULATE(max('Table'[Expiry Date]),ALL('Table'),'Table'[Product]=p,'Table'[Date]=pm))
var c = ADDCOLUMNS(b,"diff",abs([Expiry Date]-[prev expiry]))
return SWITCH(SELECTEDVALUE(Buckets[Sort])
,"4",countrows(filter(c,ISBLANK([Expiry Date])))
,"3",countrows(filter(c,[diff]=0))
,"2",countrows(filter(c,[diff]<31 && [diff]<>0))
,"1",countrows(filter(c,[diff]>=31 && not ISBLANK([Expiry Date])))
)

        see attached