Issue filtering dates power bi

[obriaincian]

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
Fanta	02/04/2023	Feb-21
Coke	08/05/2023	Mar-21
Pepsi	23/08/2024	Mar-21
7up	27/01/2023	Mar-21
Fanta	02/02/2023	Mar-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"
)

[lbendlin]

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]

@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 · Accepted solution]

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