'Filtering dates for 2 previous months Power BI
I'd like to create a table that shows the count of occurrences within a certain month.
My Data
| 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.
Desired outcome is to create a matrix visual with the below
| Change | No. of occurrences |
|---|---|
| >1 Month Change | 2 |
| <1 Month Change | 2 |
| No Change | 0 |
| No Expiry Date | 0 |
The measure I'm currently using which doesn't work is:
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"
)```
Sources
This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.
Source: Stack Overflow
| Solution | Source |
|---|
