Please Help in Creating DAX Formula

Hi everyone,

I am trying to create a DAX formula wherein I need to apply a filter and get total for specified columns in the raw data.

Here is what I am trying to accomplish:

Filter for Data A in Date 1 + Filter for Data B in Date 1 + Filter for Data A in Date 2 / (Total Data for Date 1 + Total Data for Date 2)

Wherein Date 1 is the reference date. Date 2 is the previous date.  This needs to be done for all the dates in the “Req Date” column A.

Here is an example:

Reference date is “3/1/2017” in column A (Req Date)

(1)

Filter for column A (Req Date) for reference date of “3/1/2017”.
Then filter column I (Age) to get value = “1”.
Get the total count of loads in column J (Count of Load).

(2)

Filter for column A (Req Date) for reference date of “3/1/2017”.
Then filter column I (Age) to get value = “0”.

Get the total count of loads in column J (Count of Load).

(3)

Filter for column A (Req Date) for previous date of “2/28/2017”.
Then filter column I (Age) to get value = “0”.

Get the total count of loads in column J (Count of Load).

(4)

Filter for column A (Req Date) for reference date of “3/1/2017”.
Get the total count of loads in column J (Count of Load).

 (5)

Filter for column A (Req Date) for previous date of “2/28/2017”.
Get the total count of loads in column J (Count of Load).

(6)

After getting all the results, the formula will look like this:
= (Result of Step 1 + Result of Step 2 + Result of Step 3) / (Result of Step 4 + Result of Step 5)

Below is the link of the raw data file for your reference. Please note that I am using Direct Query mode for the data.

https://www.dropbox.com/s/3snlyjsuxwwyct5/Sample%20file.xlsx?dl=0&m=

Appreciate your kind help.

Best regards,
Mark