Hi Community hope you can help, a few days searching and trying to no avail.
I am trying to create a Set Analysis that will calculate a result using a separate calendar table as the dimension.
I have 2 tables One with Data and the other a generic calendar with the usual master calendar columns.
I can get this to work with IF statements in my Pivot Table but they are slow due the volume of calculations and looking to do it with set analysis.
COUNT(IF(([Job Start Date] <= (DATADATE) AND ([Job Complete Date] <= (DATADATE))) ,[Job Number]))
My Dimension is DATADATE from the Master Calendar table so when I use the following set analysis I get null.
COUNT({<[Job Start Date] = {"<=$(DATADATE)"}, [Job Complete Date] = {"<=$(DATADATE)"}>} [Job Number])
Attached is a sample data set and what the results should be.
Any help is greatly appreciated.
