Forum Discussion

Ajithrjaa's avatar
Ajithrjaa
Frequent Visitor
2 years ago
Solved

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 Prevo...
Data Protection
Need Help
Show and Tell
Tips and Tricks
  • mickey64's avatar
    mickey64
    2 years ago

    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.

     

danextian's avatar
danextian
Super User
2 years ago

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