Dax to filter table based on touch start date and touch end date and two dates on slicer

[Brij]

Hi,
I am trying to write a DAX to check a condition to find out whether it is a STRAT WIP or END WIP

START WIP: IF "touch start date" is less than or equal to start date of date slicer AND ("touch end date" is equal to BLANK() OR "touch end date" is greater than or equal to start date of date slicer) then output should be "Y" else "N"

END WIP: IF "touch start date" is less than or equal to end date of date slicer AND ("touch end date" is equal to BLANK () OR "touch end date" is greater than or equal to end date of date slicer) then output should be "Y" else "N"

Once I find this out, I will then need to get the count of application ID to place it on a card visual to show the number of START WIP and another card to show the number of END WIP.

touch start date and touch end date is from the 'touch' table

Start date and End date from slicer is from the 'date' table

below is the scenario 

All dates are in dd/mm/yyyy format

Date sclicer range is 1/03/2022 to 5/03/2022

	Start Date	End Date
Date slicer	1/03/2022	5/03/2022
Application ID	Touch Start Date	Touch End Date	Start WIP	End WIP
A001	25/02/2022	25/02/2022	N	N
A001	25/02/2022	2/03/2022	Y	N
A002	25/02/2022		Y	Y
A002	28/02/2022	1/03/2022	Y	N
A003	1/03/2022	1/03/2022	Y	N
A004	1/03/2022	3/03/2022	Y	N
A005	2/03/2022	4/03/2022	N	N
A005	2/03/2022	5/03/2022	N	Y
A006	3/03/2022	3/03/2022	N	N
A006	4/03/2022	4/03/2022	N	N
A007	4/03/2022		N	Y
A008	5/03/2022		N	Y
OUTPUT
Start WIP for the period	5
End WIP for the period	4

[Vera_33 · Accepted solution]

Hi @Brij 

You have a Date table for the Date slicer, right?

Start WIP for the period = 
VAR CurStart= MIN('Date'[Date])
VAR T1=ADDCOLUMNS(Table2,"StartWIP", IF([Touch Start Date]<=CurStart&& OR([Touch End Date]>=CurStart,ISBLANK([Touch End Date])),1,0))
RETURN
SUMX(T1,[StartWIP])


End WIP for the period = 
VAR CurEnd=MAX('Date'[Date])
VAR T1=ADDCOLUMNS(Table2,"EndWIP", IF([Touch Start Date]<=CurEnd&& OR([Touch End Date]>=CurEnd,ISBLANK([Touch End Date])),1,0))
RETURN
SUMX(T1,[EndWIP])

[Brij · Accepted solution]

Hi @Vera_33 

Thanks for the solution. It seems fine and I've worked out my queries above.

The result of these measures returns a total number of START WIP and END WIP which is what I needed. However, what if I want to see the underlying data behind these returned number? I understand it is sitting in a virtual table so is there a way to bring in front for user to verify?

Thanks again,

Brij

[Ashish_Mathur]

Hi,

I am trying to solve the problem with measures only.  I could not get the answer as 5 and 4.  My results are turning out to be 4 and 3 which i know are incorrect but this could be a starting point for further refinement.

Hope this helps.  You may download my PBI file from here.

[Brij]

Thanks @Ashish_Mathur  I'm not able to connect to Onedrive where your file is located due to access restriction on my organisatoin. Are you able to upload it here instead? Thanks.

[Ashish_Mathur]

Please find attached.

[Vera_33]

Hi @Ashish_Mathur 

If you disconnect your Calendar with Data table, then it works

[Ashish_Mathur]

Hi,

Thank you for replying.  I wonder why that happens.  Why is the Calendar Table proving to be a bottleneck?

[Brij · Accepted solution]

Hi @Vera_33 

Thanks for the solution. It seems fine and I've worked out my queries above.

The result of these measures returns a total number of START WIP and END WIP which is what I needed. However, what if I want to see the underlying data behind these returned number? I understand it is sitting in a virtual table so is there a way to bring in front for user to verify?

Thanks again,

Brij

[Brij · Accepted solution]

Hi @Vera_33 , I've worked this out to see the underlying data of these measure. I've created a new report page and then on the table I've added require columns from the original table. Use these measures as drillthrough. Filter the table with require result from these measure. It works. Thanks for your help!

[Vera_33]

Hi @Brij 

You want to show it in a visual, like this?

Start WIP = 
VAR CurStart=MIN('Date'[Date])
VAR CurStartDate=MAX([Touch Start Date])
VAR CurEndDate=MAX([Touch End Date])
RETURN
IF(CurStartDate<=CurStart&& OR(CurEndDate>=CurStart,ISBLANK(CurEndDate)),1,0)

[Brij]

Hi @Vera_33 

Thanks for the solution and your prompt reply.

Yes, this is what I like to see in a tabular form, just to verify the total number. However, when I applied the same in my model it is not giving me the same result!😕 See below screenshot;

[Vera_33]

Hi @Brij 

Can you check the file shared by @Ashish_Mathur , see if it is the same shape as your dataset?

[Vera_33 · Accepted solution]

Hi @Brij 

You have a Date table for the Date slicer, right?

Start WIP for the period = 
VAR CurStart= MIN('Date'[Date])
VAR T1=ADDCOLUMNS(Table2,"StartWIP", IF([Touch Start Date]<=CurStart&& OR([Touch End Date]>=CurStart,ISBLANK([Touch End Date])),1,0))
RETURN
SUMX(T1,[StartWIP])


End WIP for the period = 
VAR CurEnd=MAX('Date'[Date])
VAR T1=ADDCOLUMNS(Table2,"EndWIP", IF([Touch Start Date]<=CurEnd&& OR([Touch End Date]>=CurEnd,ISBLANK([Touch End Date])),1,0))
RETURN
SUMX(T1,[EndWIP])

[Brij]

Hi, Thanks,

Yes, I do have a date table.

Just checking is this a measure or a calculated column? Also Table 2 in your DAX is my 'touch' table, right?