Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
3 years ago
Solved

Condition based date range data loading based on month selection

Hi all,                 Creating a report using Postgresql DB.Table Visual data is loading using folllowing sample query.    select column1,colum2,expiry_date,effective_date from table where expi...
Need Help
Show and Tell
  • v-easonf-msft's avatar
    v-easonf-msft
    3 years ago

    Hi, Anonymous 

    If you have a seperate Calendar table, you can put a measure to the table visual filter pane to filter data.

    filter = 
VAR _date =
    EDATE ( MAX ( 'Calendar'[Date] ), -1 )
VAR a =
    IF (
        MAX ( 'Fact Table'[effective_date] ) <= _date
            && MAX ( 'Fact Table'[expiry_date] ) >= _date
            && MAX ( 'Fact Table'[expiry_date] ) < EDATE ( _date, 12 ),
        1,
        0
    )
RETURN
    IF ( ISFILTERED ( 'Calendar'[Date].[Month] ), a, 1 )

    Please check my sample for more details.

     

    Best Regards,
    Community Support Team _ Eason
    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

v-easonf-msft's avatar
v-easonf-msft
Icon for Community Support rankCommunity Support
3 years ago

Hi, Anonymous 

If you have a seperate Calendar table, you can put a measure to the table visual filter pane to filter data.

filter = 
VAR _date =
    EDATE ( MAX ( 'Calendar'[Date] ), -1 )
VAR a =
    IF (
        MAX ( 'Fact Table'[effective_date] ) <= _date
            && MAX ( 'Fact Table'[expiry_date] ) >= _date
            && MAX ( 'Fact Table'[expiry_date] ) < EDATE ( _date, 12 ),
        1,
        0
    )
RETURN
    IF ( ISFILTERED ( 'Calendar'[Date].[Month] ), a, 1 )

Please check my sample for more details.

 

Best Regards,
Community Support Team _ Eason
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.