Create a Flag measure based on Start date and End Date.

[Anonymous]

Hello All,

I have a table and I need to show records between start date and End date. 

Need to create a flag which has to be 1 for dates which is between start date and End Date. So that I can drop flag in measure and select "1" to show the records. 

Below is the Dax I used but gettting error "Multiple Values supplied instead of one"

Flag =

Var startdate=SELECTEDVALUE('Selection Values'[CAL_DT])

var Startdate_plus = startdate+1

Var PStrt_date= startdate-365

var enddate= startdate+14
return IF(AND(VALUES(V_DAILY_SERVICE_CALL_LOG[CAL_DT]) >= IF(VALUES(V_DAILY_SERVICE_CALL_LOG[STATUS_ID]) IN {"A501","A405","A411"},PStrt_date,Startdate_plus),VALUES(V_DAILY_SERVICE_CALL_LOG[CAL_DT]) <= enddate),1,0

Here (V_DAILY_SERVICE_CALL_LOG[CAL_DT] is retireved by using lookup from date table.

Below is the model:

My fact table is V_DAILY_SERVICE_CALL_LOG. Date slicer is coming from a separate table which is not in relation with Fact table and Service dest is coming from a Dimension table which is in One to Many relation with Fact.

Relative SQL query for flag is below, I want to convert that Flag query to Flag measure so that I can drop this to Table Vizualization filter pane:-

Select * from(
Select f.*,
CASE When d.CAL_DT BETWEEN (CASE WHEN STATUS_ID IN ('A501','A405','A411') THEN DATEADD(DAY,-365,'2023-03-04') ELSE DATEADD(DAY,1,'2023-03-04') END) AND DATEADD(DAY,14,'2023-03-04') THEN 1 ELSE 0 END as Flag
from "LESL_DW_UAT"."PBI_MART"."V_DAILY_SERVICE_CALL_LOG" f
inner join "LESL_DW_UAT"."PBI_MART"."V_DATE_DIM" d on f.DT_SKEY = d.DT_SKEY
inner join "LESL_DW_UAT"."PBI_MART"."V_STORE_SERVICE_DIM" s on f.STORE_KEY = s.STORE_KEY
where
s.SERVICE_DSTRCT_CD ='22' ) A where Flag = 1

[Anonymous]

@v-tangjie-msft ,

Thanks for the response.
In my case, There are two conditions

 date between status_id in ('A501','A405','A411') then start_date -365 else start_date+1 and End_date.

I added a sample pbix. please download pbix with sample data.

https://drive.google.com/file/d/1l17xTVn0iPwSZfB_mODdcljxKwf0dAVY/view?usp=share_link

[v-tangjie-msft · Accepted solution]

Hi @Anonymous ,

According to your description, here are my steps you can follow as a solution.

(1) My test data is the same as yours.

(2) We can create a measure.

Flag = 
Var startdate=SELECTEDVALUE('Selection Values'[CAL_DT])
var Startdate_plus = startdate+1
Var PStrt_date= startdate-365
var enddate= startdate+14
var _sdate=IF(MAX('Fact'[STATUS_ID]) in {"A501","A405","A411"},PStrt_date,Startdate_plus)
return 
IF(MAX('Fact'[CAL_DT])>=_sdate && MAX('Fact'[CAL_DT])<=enddate,1,0)

(3) Then the result is as follows.

Best Regards,

Neeko Tang

If this post  helps, then please consider Accept it as the solution  to help the other members find it more quickly. 

[v-tangjie-msft]

Hi @Anonymous ,

According to your description, here are my steps you can follow as a solution.

(1) This is my test data. 

(2) We can create a measure. 

Flag = 
var _startdate=CALCULATE(MAX('Selection Values'[CAL_DT]),FILTER(ALL('Selection Values'),'Selection Values'[STATUS_ID]=MAX('V_DAILY_SERVICE_CALL_LOG'[STATUS_ID])))
var _enddate=_startdate+14
var _flag=IF(SELECTEDVALUE('V_DAILY_SERVICE_CALL_LOG'[CAL_DT])>=_startdate && SELECTEDVALUE(V_DAILY_SERVICE_CALL_LOG[CAL_DT])<=_enddate,1,0)
return _flag

(3) Then the result is as follows.

If the above one can't help you get the desired result, please provide some sample data in your tables (exclude sensitive data) with Text format and your expected result with backend logic and special examples. It is better if you can share a simplified pbix file. Thank you.

Best Regards,

Neeko Tang

If this post  helps, then please consider Accept it as the solution  to help the other members find it more quickly. 

[Wilson_]

Hello,

Any chance you can share your pbix (or at least a smaller sample version)?

[Anonymous]

Hi @Wilson_ ,

https://drive.google.com/file/d/1l17xTVn0iPwSZfB_mODdcljxKwf0dAVY/view?usp=share_link

Please find the attched pbix