Power BI is turning 10! Tune in for a special live episode on July 24 with behind-the-scenes stories, product evolution highlights, and a sneak peek at what’s in store for the future.
Save the dateJoin 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.
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"
)
Solved! Go to 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
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 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
Check out the July 2025 Power BI update to learn about new features.
This is your chance to engage directly with the engineering team behind Fabric and Power BI. Share your experiences and shape the future.
User | Count |
---|---|
62 | |
61 | |
54 | |
38 | |
26 |
User | Count |
---|---|
84 | |
60 | |
45 | |
41 | |
39 |