DAX Calculating Average

[Bonjuga]

Hi Guys,

I have a database that has data looks like following:

Transaction ID	Status	Days
1	received	1
1	sent	2
2	received	3
2	sent	4
2	received from IR	5
3	received	6
3	sent	7
3	received from IR	8
4	received	9
4	sent	10

so, for each transaction, there are either two or three status: :"received", "sent", or "received from IR".  Now I am very interested in average time between "received from IR" and "sent".  That means, I need to extract those Transactions that have " received from IR"(for example, transaction 2 and 3), then use "received from IR" days minus "sent" days. 

The expected output would be ((5-4)+(8-7))/2, so it is (("received from IR" from transaction 2- "sent" from transaction2)+("received from IR" from transaction 3 - "sent" from transaction 3))/2

Is there anyway I could do that with DAX?  Also, I am currently using Azure database, which means I cannot create new column, I can only create new meausres.

Please let me know if there are any questions.  Thanks.

[v-yulgu-msft · Accepted solution]

Hi @Bonjuga,

Please try below measures:

days for received IR =
CALCULATE (
    SUM ( Sheet2[Days] ),
    FILTER ( Sheet2, Sheet2[Status] = "received from IR" )
)

days for sent =
CALCULATE (
    SUM ( Sheet2[Days] ),
    FILTER (
        FILTER (
            Sheet2,
            CALCULATE (
                COUNT ( Sheet2[Transaction ID] ),
                ALLEXCEPT ( Sheet2, Sheet2[Transaction ID] )
            )
                = 3
        ),
        Sheet2[Status] = "sent"
    )
)

Count transaction =
CALCULATE (
    DISTINCTCOUNT ( Sheet2[Transaction ID] ),
    FILTER ( Sheet2, Sheet2[Status] = "received from IR" )
)

Average = ([days for received IR]-[days for sent])/[Count transaction]

Best regards,
Yuliana Gu

[Bonjuga]

Thanks!!!!!!! That works!!!!!!! You just solved a huge problem of mine!! Thanks!!!!!

[Bonjuga]

Hi Guys,

I have a database that has data looks like following:

Transaction ID	Status	Days
1	received	1
1	sent	2
2	received	3
2	sent	4
2	received from IR	5
3	received	6
3	sent	7
3	received from IR	8
4	received	9
4	sent	10

so, for each transaction, there are either two or three status: :"received", "sent", or "received from IR".  Now I am very interested in average time between "received from IR" and "sent".  That means, I need to extract those Transactions that have " received from IR"(for example, transaction 2 and 3), then use "received from IR" days minus "sent" days. 

The expected output would be ((5-4)+(8-7))/2, so it is (("received from IR" from transaction 2- "sent" from transaction2)+("received from IR" from transaction 3 - "sent" from transaction 3))/2

Is there anyway I could do that with DAX?  Also, I am currently using Azure database, which means I cannot create new column, I can only create new meausres.

[Bonjuga]

I have a database that has data looks like following:

Transaction ID	Status	Days
1	received	1
1	sent	2
2	received	3
2	sent	4
2	received from IR	5
3	received	6
3	sent	7
3	received from IR	8
4	received	9
4	sent	10

so, for each transaction, there are either two or three status: :"received", "sent", or "received from IR".  Now I am very interested in average time between "received from IR" and "sent".  That means, I need to extract those Transactions that have " received from IR"(for example, transaction 2 and 3), then use "received from IR" days minus "sent" days. 

The expected output would be ((5-4)+(8-7))/2, so it is (("received from IR" from transaction 2- "sent" from transaction2)+("received from IR" from transaction 3 - "sent" from transaction 3))/2

Is there anyway I could do that with DAX?  Also, I am currently using Azure database, which means I cannot create new column, I can only create new meausres.

[Greg_Deckler]

You should be able to use CALCULATE with a measure to do what you want. So, for example:

Measure = CALCULATE(SUM([Days]),FILTER(Table,[Status]="received"))

You could create a similar one for "sent" and then perhaps a third measure to do your calculation?

[Bonjuga]

Thanks for your reply!!  However, I don't want transaction 1 and 4, how can I filter them out so they won't affect my calculation?

[GilbertQ]

Hi there, thanks for the details, would it be possible to post what you expect the output to look like?

Makes it easier to find a working solution.

[Bonjuga]

Thanks for your advice!  Just updated question!