Forum Discussion
Create a Flag measure based on Start date and End Date.
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"
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
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
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.
5 Replies
Wilson_Memorable Member
Hello,
Any chance you can share your pbix (or at least a smaller sample version)?
Hi Wilson_ ,
https://drive.google.com/file/d/1l17xTVn0iPwSZfB_mODdcljxKwf0dAVY/view?usp=share_link
Please find the attched pbix
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.
Anonymous ,
Thanks for the response.
In my case, There are two conditionsdate 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_linkHi 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.