Please Help in Creating DAX Formula

[markefrody]

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

[v-chuncz-msft]

@markefrody,

You may start from the SELECTEDVALUE Function.

https://www.sqlbi.com/articles/using-the-selectedvalue-function-in-dax/

[markefrody]

@v-chuncz-msft

Thanks for the link but I am a beginner at writing DAX codes. Will you be able to help me in writing the code as per my specifications above? Appreciate your kind assistance.

[v-chuncz-msft · Accepted solution]

@markefrody,

Refer to the measure below.

Measure =
VAR d =
    SELECTEDVALUE ( 'Calendar'[Date] )
RETURN
    DIVIDE (
        SUMX (
            FILTER (
                Table1,
                ( Table1[Req Date] = d
                    && Table1[Age] IN { 0, 1 } )
                    || ( Table1[Req Date]
                    = d - 1
                    && Table1[Age] = 0 )
            ),
            Table1[Count of Load]
        ),
        SUMX (
            FILTER ( Table1, Table1[Req Date] IN { d, d - 1 } ),
            Table1[Count of Load]
        )
    )