Forum Discussion
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 expiry_date>='09/30/2022'
and expiry_date<'09/30/2023' and effective_date<='09/30/2022'.
Here for reference taken a sample query for the september month data. But i will have all month data as backup. Using a month slicer here.
User will select a month from slicer .Suppose selected is october month means ,data should be take from expiry_date>='09/30/2022'
and expiry_date<'09/30/2023' and effective_date<='09/30/2022'.
Previous month date range data should be load in table visual.
Is it possible?
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.
4 Replies
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.Using postgresql database creating a report in power bi. Database select query contain some date conditions like below
Plcy_exp_date>='09/30/2022' and Plcy_exp_date<'09/30/2023' And "EFF_DATE"<='09/30/2022' .
So, I have slicer in report which having month values.If the user select a month ,it will load based on the date condition above.
Is there any way to do?
Hi Anonymous ,
You can pass the slicer value to database query by Dynamic parameters please check the documentation
https://learn.microsoft.com/en-us/power-bi/connect-data/desktop-dynamic-m-query-parameters
If this post helps, then please consider Accept it as the solution, Appreciate your Kudos!!
Anonymous You need a date filter table that's not related to your data at all. Add at least two columns for:
Month Name
End of Month Date
Then use this blog to apply a similar technique to your data:
https://excelwithallison.blogspot.com/2021/08/categorical-date-slicer-in-power-bi.html
