Date slicer with few conditions

[PavaniD]

Hello

I have a SQL query ,under the WHERE condition the date slicer have few conditions.How can i achieve that in power bi?Below is the query.

WHERE

(FA.CRE_DTTM) >=
CASE WHEN #PROMPT('PARAM1')# = TO_CHAR(SYSDATE , 'YYYY-MM-DD') THEN (CURRENT_DATE - 90 )
ELSE TO_DATE(#PROMPT('PARAM1')#, 'YYYY-MM-DD')
END
AND (FA.CRE_DTTM) <=
CASE WHEN #PROMPT('PARAM2')# = TO_CHAR(SYSDATE, 'YYYY-MM-DD') THEN (CURRENT_DATE +1)
ELSE TO_DATE(#PROMPT('PARAM2')#, 'YYYY-MM-DD') +1
END

[PavaniD]

Thanks  @rajendraongole1. Can I create measure or column ?
also I need only date and cre_dttm has only date.

[rajendraongole1]

Hi @PavaniD you have to create a parameters in Power query editor as like PARAM1 and param2.

Try the above approach.

Did I answer your question? Mark my post as a solution! This will help others on the forum!
Appreciate your Kudos!!

[PavaniD]

In this approach it will be modifying complete data set right? I want this condition only for one report and i will be creating the parameters in data model.

[rajendraongole1]

Hi @PavaniD -you can achieve the date filtering logic similar to your SQL query in Power BI using parameters and Power Query Editor.

Create two parameters in Power BI, one for PARAM1 and another for PARAM2. Make sure to set the data type of these parameters to Date/Time and configure the default values as required.

In your power query editor, below steps should be added 
Param1 = DateTime.FromText(Text.From(Date.From(DateTime.LocalNow())),
Param2 = DateTime.FromText(Text.From(Date.From(DateTime.LocalNow()))),
FilteredRows = Table.SelectRows(Source, each ([CRE_DTTM] >= if [CRE_DTTM] = Param1 then Date.AddDays(DateTime.LocalNow(), -90) else Param1
and [CRE_DTTM] <= if [CRE_DTTM] = Param2 then Date.AddDays(DateTime.LocalNow(), 1) else Param2))

This script will filter the rows based on the parameters PARAM1 and PARAM2. It compares the values of [CRE_DTTM] with the parameters and applies the filtering logic accordingly.

Did I answer your question? Mark my post as a solution! This will help others on the forum!
Appreciate your Kudos!!