Forum Discussion
Data Filter Condition
Hi ,
I have a some quey please add some valid use casess on this scenarion.
Case : Max Year is 2024 from that I have a min date 1/4/2024 and Max Date 7/11/2024 this is the data range for Prevoius Years.
I would Like Put a one Condition Previous Years also Baesd on this dynamics Min and Max DDMM.
the MinDate and Max Date range should be a Maxdate Year.
| Date | Sales | Year |
| 11/2/2022 | 61 | 2022 |
| 7/27/2022 | 33 | 2022 |
| 8/18/2022 | 67 | 2022 |
| 11/24/2022 | 94 | 2022 |
| 7/5/2022 | 93 | 2022 |
| 1/19/2022 | 51 | 2022 |
| 8/22/2022 | 67 | 2022 |
| 5/31/2022 | 69 | 2022 |
| 8/4/2022 | 11 | 2022 |
| 8/4/2022 | 46 | 2022 |
| 4/4/2022 | 8 | 2022 |
| 1/11/2022 | 6 | 2022 |
| 1/15/2022 | 36 | 2022 |
| 9/23/2022 | 63 | 2022 |
| 9/11/2022 | 94 | 2022 |
| 11/13/2022 | 29 | 2022 |
| 5/22/2023 | 100 | 2023 |
| 10/26/2023 | 78 | 2023 |
| 12/5/2023 | 84 | 2023 |
| 7/13/2023 | 5 | 2023 |
| 7/7/2023 | 85 | 2023 |
| 11/24/2023 | 51 | 2023 |
| 2/7/2023 | 14 | 2023 |
| 8/29/2023 | 44 | 2023 |
| 5/31/2024 | 24 | 2024 |
| 1/4/2024 | 36 | 2024 |
| 3/9/2024 | 43 | 2024 |
| 2/1/2024 | 87 | 2024 |
| 7/11/2024 | 8 | 2024 |
| 2/16/2024 | 19 | 2024 |
Output Like
| Date | Sales | Year | Flag |
| 5/31/2024 | 24 | 2024 | Y |
| 1/4/2024 | 36 | 2024 | Y |
| 3/9/2024 | 43 | 2024 | Y |
| 2/1/2024 | 87 | 2024 | Y |
| 7/11/2024 | 8 | 2024 | Y |
| 2/16/2024 | 19 | 2024 | Y |
| 5/22/2023 | 100 | 2023 | Y |
| 10/26/2023 | 78 | 2023 | N |
| 12/5/2023 | 84 | 2023 | N |
| 7/13/2023 | 5 | 2023 | N |
| 7/7/2023 | 85 | 2023 | Y |
| 11/24/2023 | 51 | 2023 | N |
| 2/7/2023 | 14 | 2023 | Y |
| 8/29/2023 | 44 | 2023 | N |
Step 0: I use your data. (Date:yyyy/mm/dd)
Step 1: I make a 'Calendar' table and add a relationship.
Calendar = CALENDAR(FIRSTDATE('DATA'[Date]),LASTDATE('DATA'[Date]))
Step 2: I make a 'Sel-Cal' table and make a 'Sel-Cal' slicer.
Sel-Cal = SUMMARIZE('DATA','DATA'[Year])
- Slicer -
Step 3: I make many measures below.
M_Flag = IF(MAX('DATA'[Date])<=[M_Maxx_Date_ALL]&&MIN('DATA'[Date])>=[M_Minx_Date_ALL],"Y",IF(MAX('DATA'[Date])<=[M_Maxx_Date_Pre_ALL]&&MIN('DATA'[Date])>=[M_Minx_Date_Pre_ALL],"Y","N"))
M_Maxx_Date_ALL = MAXX(FILTER(ALL('DATA'),'DATA'[Year]=[M_Year_Sel]),'DATA'[Date])
M_Maxx_Date_Pre_ALL = MAXX(FILTER(ALL('Calendar'),(YEAR('Calendar'[Date])=[M_Year_Sel]-1)&&('Calendar'[Date]<=[M_Max_YMD_Pre])),'Calendar'[Date])
M_Minx_Date_ALL = MINX(FILTER(ALL('DATA'),'DATA'[Year]=[M_Year_Sel]),'DATA'[Date])
M_Minx_Date_Pre_ALL = MINX(FILTER(ALL('Calendar'),(YEAR('Calendar'[Date])=[M_Year_Sel]-1)&&('Calendar'[Date]>=[M_Min_YMD_Pre])),'Calendar'[Date])
M_Max_YMD_Pre = DATE([M_Year_Sel]-1,[M_Max_M_ALL],[M_Max_D_ALL])
M_Min_YMD_Pre = DATE([M_Year_Sel]-1,[M_Min_M_ALL],[M_Min_D_ALL])
Step 4: I make a matrix.
3 Replies
Hi Ajithrjaa ,
I am not exactly sure what you are trying to achieve but if you want to check whether the current period (month and year) has data in the previous year (previous year and month), you can try the calculated column below:
Flag = VAR PreviousYearsPeriod = FORMAT ( EDATE ( 'Table'[Date], -12 ), "YYYYMM" ) VAR PreviousYearsValue = CALCULATE ( SUM ( 'Table'[Sales] ), FILTER ( ALL ( 'Table' ), FORMAT ( 'Table'[Date], "YYYYMM" ) = PreviousYearsPeriod ) ) RETURN IF ( NOT ( ISBLANK ( PreviousYearsValue ) ), "Y", "N" )Step 0: I use your data. (Date:yyyy/mm/dd)
Step 1: I make a 'Calendar' table and add a relationship.
Calendar = CALENDAR(FIRSTDATE('DATA'[Date]),LASTDATE('DATA'[Date]))
Step 2: I make a 'Sel-Cal' table and make a 'Sel-Cal' slicer.
Sel-Cal = SUMMARIZE('DATA','DATA'[Year])
- Slicer -
Step 3: I make many measures below.
M_Flag = IF(MAX('DATA'[Date])<=[M_Maxx_Date_ALL]&&MIN('DATA'[Date])>=[M_Minx_Date_ALL],"Y",IF(MAX('DATA'[Date])<=[M_Maxx_Date_Pre_ALL]&&MIN('DATA'[Date])>=[M_Minx_Date_Pre_ALL],"Y","N"))
M_Maxx_Date_ALL = MAXX(FILTER(ALL('DATA'),'DATA'[Year]=[M_Year_Sel]),'DATA'[Date])
M_Maxx_Date_Pre_ALL = MAXX(FILTER(ALL('Calendar'),(YEAR('Calendar'[Date])=[M_Year_Sel]-1)&&('Calendar'[Date]<=[M_Max_YMD_Pre])),'Calendar'[Date])
M_Minx_Date_ALL = MINX(FILTER(ALL('DATA'),'DATA'[Year]=[M_Year_Sel]),'DATA'[Date])
M_Minx_Date_Pre_ALL = MINX(FILTER(ALL('Calendar'),(YEAR('Calendar'[Date])=[M_Year_Sel]-1)&&('Calendar'[Date]>=[M_Min_YMD_Pre])),'Calendar'[Date])
M_Max_YMD_Pre = DATE([M_Year_Sel]-1,[M_Max_M_ALL],[M_Max_D_ALL])
M_Min_YMD_Pre = DATE([M_Year_Sel]-1,[M_Min_M_ALL],[M_Min_D_ALL])
Step 4: I make a matrix.
