Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.

Reply
anupaminfo
Frequent Visitor

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. 

anupaminfo_0-1680798981217.png

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:
anupaminfo_1-1680799711873.png

 

 
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
1 ACCEPTED SOLUTION

Hi @anupaminfo ,

 

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.

vtangjiemsft_0-1681286214827.png

 

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. 

View solution in original post

5 REPLIES 5
anupaminfo
Frequent Visitor

@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

Hi @anupaminfo ,

 

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.

vtangjiemsft_0-1681286214827.png

 

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
Community Support
Community Support

Hi @anupaminfo ,

 

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

(1) This is my test data. 

vtangjiemsft_0-1681279033479.pngvtangjiemsft_1-1681279046082.png

(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.

vtangjiemsft_2-1681279112618.png

 

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_
Solution Sage
Solution Sage

Hello,

 

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

Helpful resources

Announcements
Microsoft Fabric Learn Together

Microsoft Fabric Learn Together

Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City

PBI_APRIL_CAROUSEL1

Power BI Monthly Update - April 2024

Check out the April 2024 Power BI update to learn about new features.

April Fabric Community Update

Fabric Community Update - April 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors