Query with subquery also need to filter out while click on month slicer for previous data

[Anonymous]

Hi all,

              Creating a report using Postgresql DB.Table Visual data is loading using folllowing sample query. 

select temp.risk_type,temp.expiry_date,temp.effective_date

                              (select risk_type,Rank() OVER(PARTITION BY policyno ORDER BY ver_no DESC) AS rn,

                               expiry_date,effective_date from table

                               where expiry_date>='09/30/2022' and expiry_date<'09/30/2023' and effective_date<='09/30/2022')

                                 as temp

 where temp.rn=1 and temp.risk_type != 'Cancel' or (temp.risk_type = 'Cancel' and temp.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'.

Also outer query condition need to check before loading report.

Previous month date range data should be load in table visual.

Is it possible?

[amitchandak · Accepted solution]

@Anonymous , Create an independent date table and so not join it with other dates

then you can have a measure like

//Date1 is independent Date table
new measure =
var _max = maxx(allselected(Date1),Date1[Date])
var _min = minx(allselected(Date1),Date1[Date])

var _max1 = date(year(_max)+1, Month(_max), Day(_max))
return
calculate( sum(Table[Value]), filter('Table', 'Table'[expiry_date] >=_max&& 'Table'[expiry_date] <=_max1 && [effective_date] <= _max ))

refer if needed

Need of an Independent Date Table:https://www.youtube.com/watch?v=44fGGmg9fHI