cancel
Showing results for
Did you mean:
Member

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=

Best regards,
Mark

1 ACCEPTED SOLUTION

Accepted Solutions
Community Support Team

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 )
),
),
SUMX (
FILTER ( Table1, Table1[Req Date] IN { d, d - 1 } ),
)
)
```
Community Support Team _ Sam Zha
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
3 REPLIES 3
Community Support Team

You may start from the SELECTEDVALUE Function.

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

Community Support Team _ Sam Zha
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Member

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

Community Support Team

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 )
),