Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Join us for an expert-led overview of the tools and concepts you'll need to become a Certified Power BI Data Analyst and pass exam PL-300. Register now.

Reply
obriaincian
Resolver I
Resolver I

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"
)

 

 

1 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

View solution in original post

3 REPLIES 3
lbendlin
Super User
Super User

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"

 

lbendlin_0-1649632733741.png

 

@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

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

Helpful resources

Announcements
July PBI25 Carousel

Power BI Monthly Update - July 2025

Check out the July 2025 Power BI update to learn about new features.

Join our Fabric User Panel

Join our Fabric User Panel

This is your chance to engage directly with the engineering team behind Fabric and Power BI. Share your experiences and shape the future.

June 2025 community update carousel

Fabric Community Update - June 2025

Find out what's new and trending in the Fabric community.