'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 |
---|