Value, Earlier, Allexcept ?

P

propi_

New Member
Joined
Jun 11, 2014
Messages
19
Hey guys,

can you help me with the following please:

I want to figuere out the first and second Order for each Customer and put it to a column.

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]ID[/TD]
[TD="width: 124"]Customer_id[/TD]
[TD="width: 122"]Order_of_n_day[/TD]
[TD="width: 80"]OrderDay[/TD]
[TD="width: 80"]Orderstring[/TD]
[TD="width: 80"]Revenue[/TD]
[TD="width: 106"]Date[/TD]
[TD="width: 80"]Order no[/TD]
[TD="width: 80"]Category[/TD]
[TD="width: 119"]FirstDayfirstOrder[/TD]
[TD="width: 119"]SecondDay1Order[/TD]
[/TR]
[TR]
[TD="align: right"]1[/TD]
[TD="align: right"]100[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]1[/TD]
[TD]11
[/TD]
[TD="align: right"]27[/TD]
[TD="align: right"]29.07.2015 00:01[/TD]
[TD="align: right"]885689[/TD]
[TD]V[/TD]
[TD]V
[/TD]
[TD]S
[/TD]
[/TR]
[TR]
[TD="align: right"]2[/TD]
[TD="align: right"]100[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]1[/TD]
[TD]21[/TD]
[TD="align: right"]22,36[/TD]
[TD="align: right"]29.07.2015 00:05[/TD]
[TD="align: right"]885690[/TD]
[TD]F[/TD]
[TD]V[/TD]
[TD]S[/TD]
[/TR]
[TR]
[TD="align: right"]3[/TD]
[TD="align: right"]100[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]1[/TD]
[TD]31[/TD]
[TD="align: right"]49,19[/TD]
[TD="align: right"]29.07.2015 00:06[/TD]
[TD="align: right"]885691[/TD]
[TD]P[/TD]
[TD]V[/TD]
[TD]S[/TD]
[/TR]
[TR]
[TD="align: right"]4[/TD]
[TD="align: right"]100[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]1[/TD]
[TD]41[/TD]
[TD="align: right"]18,02[/TD]
[TD="align: right"]29.07.2015 00:07[/TD]
[TD="align: right"]885692[/TD]
[TD]F[/TD]
[TD]V[/TD]
[TD]S[/TD]
[/TR]
[TR]
[TD="align: right"]5[/TD]
[TD="align: right"]100[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]2[/TD]
[TD]12
[/TD]
[TD="align: right"]108,4[/TD]
[TD="align: right"]01.09.2015 00:01[/TD]
[TD="align: right"]885693[/TD]
[TD]S[/TD]
[TD]V[/TD]
[TD]S[/TD]
[/TR]
[TR]
[TD="align: right"]6[/TD]
[TD="align: right"]100[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]3[/TD]
[TD]13[/TD]
[TD="align: right"]27[/TD]
[TD="align: right"]07.11.2016 00:01[/TD]
[TD="align: right"]885694[/TD]
[TD]V[/TD]
[TD]V[/TD]
[TD]S[/TD]
[/TR]
[TR]
[TD="align: right"]7[/TD]
[TD="align: right"]100[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]3[/TD]
[TD]23[/TD]
[TD="align: right"]60,17[/TD]
[TD="align: right"]07.11.2016 00:05[/TD]
[TD="align: right"]885695[/TD]
[TD]P[/TD]
[TD]V[/TD]
[TD]S[/TD]
[/TR]
[TR]
[TD="align: right"]8[/TD]
[TD="align: right"]100[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]3[/TD]
[TD]33[/TD]
[TD="align: right"]100[/TD]
[TD="align: right"]07.11.2016 00:06[/TD]
[TD="align: right"]885696[/TD]
[TD]V[/TD]
[TD]V[/TD]
[TD]S[/TD]
[/TR]
[TR]
[TD="align: right"]9[/TD]
[TD="align: right"]200[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]1[/TD]
[TD]11
[/TD]
[TD="align: right"]27[/TD]
[TD="align: right"]29.08.2015 00:01[/TD]
[TD="align: right"]885697[/TD]
[TD]X[/TD]
[TD]X[/TD]
[TD]Z[/TD]
[/TR]
[TR]
[TD="align: right"]10[/TD]
[TD="align: right"]200[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]1[/TD]
[TD]21[/TD]
[TD="align: right"]22,36[/TD]
[TD="align: right"]29.08.2015 00:05[/TD]
[TD="align: right"]885698[/TD]
[TD]F[/TD]
[TD]X[/TD]
[TD]Z[/TD]
[/TR]
[TR]
[TD="align: right"]11[/TD]
[TD="align: right"]200[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]1[/TD]
[TD]31[/TD]
[TD="align: right"]50[/TD]
[TD="align: right"]29.08.2015 00:06[/TD]
[TD="align: right"]885699[/TD]
[TD]P[/TD]
[TD]X[/TD]
[TD]Z[/TD]
[/TR]
[TR]
[TD="align: right"]12[/TD]
[TD="align: right"]200[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]1[/TD]
[TD]41[/TD]
[TD="align: right"]18,02[/TD]
[TD="align: right"]29.08.2015 00:07[/TD]
[TD="align: right"]885700[/TD]
[TD]F[/TD]
[TD]X[/TD]
[TD]Z[/TD]
[/TR]
[TR]
[TD="align: right"]13[/TD]
[TD="align: right"]200[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]2[/TD]
[TD]12[/TD]
[TD="align: right"]200[/TD]
[TD="align: right"]01.10.2015 00:01[/TD]
[TD="align: right"]885701[/TD]
[TD]Z[/TD]
[TD]X[/TD]
[TD]Z[/TD]
[/TR]
[TR]
[TD="align: right"]14[/TD]
[TD="align: right"]200[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]3[/TD]
[TD]13[/TD]
[TD="align: right"]27[/TD]
[TD="align: right"]17.11.2016 00:01[/TD]
[TD="align: right"]885702[/TD]
[TD]V[/TD]
[TD]X[/TD]
[TD]Z[/TD]
[/TR]
[TR]
[TD="align: right"]15[/TD]
[TD="align: right"]200[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]3[/TD]
[TD]23[/TD]
[TD="align: right"]60,17[/TD]
[TD="align: right"]17.11.2016 00:05[/TD]
[TD="align: right"]885703[/TD]
[TD]P[/TD]
[TD]X[/TD]
[TD]Z[/TD]
[/TR]
[TR]
[TD="align: right"]16[/TD]
[TD="align: right"]200[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]3[/TD]
[TD]33[/TD]
[TD="align: right"]200[/TD]
[TD="align: right"]17.11.2016 00:06[/TD]
[TD="align: right"]885704[/TD]
[TD]V[/TD]
[TD]X[/TD]
[TD]Z
[/TD]
[/TR]
</tbody>[/TABLE]


Thank you guy, you saved me some extra joins in SQL :)
Not sure if the DAX Engine performs faster than an SQL Statement...

Greets
Andy
 

Excel Facts

Copy a format multiple times
Select a formatted range. Double-click the Format Painter (left side of Home tab). You can paste formatting multiple times. Esc to stop
Sort by date Sort by votes
Hi Andy,

Just to interpret what you are wanting to calculate:

  1. For the current Customer, return he Category of the first order on the first date where that Customer placed an order.
  2. For the current Customer, return he Category of the first order on the second date where that Customer placed an order.

Using the index columns you already have, there are a few options:

Code: 
[B]FirstDayFirstOrder[/B]
=
CALCULATE (
    FIRSTNONBLANK ( Data[Category], 0 ),
    ALLEXCEPT ( Data, Data[Customer_id] ),
    Data[OrderDay] = 1,
    Data[Order_of_n_day] = 1
)
or
Code: 
[B]FirstDayFirstOrder[/B]
=
CALCULATE (
    FIRSTNONBLANK ( Data[Category], 0 ),
    ALLEXCEPT ( Data, Data[Customer_id] ),
    Data[OrderString] = "11"
)
or
Code: 
[B]FirstDayFirstOrder[/B]
=
LOOKUPVALUE (
    Data[Category],
    Data[Customer_id], Data[Customer_id],
    Data[Orderstring], "11"
)

The SecondDayFirstOrder measure would be the same but with Data[OrderDay] = 2 or Data[OrderString] = "12"

I assume, since you are adding calculated columns, you will want to filter by these columns in your report at some point?

Regards,
Owen :)
 
Last edited:
Upvote 0
Hi Owen,

yes filter on these columns were the goal...
Man you're the best! FIRSTNONBLANK() was the key for me.
Even the LOOKUPVALUE() makes it easy though...

You've got my MVP Vote :)

Regards
Andy
 
Upvote 0
You must log in or register to reply here.

Similar threads

U
  • Question Question
Power Query chose a value <=date
Replies
1
Views
196
AlienSx
A
K
  • Question Question
Power bi Calculate the Monday dates
Replies
1
Views
124
GraH
GraH
R
  • Question Question
Dynamic Unit Price DAX Measure
Replies
0
Views
222
radonwilson
R
J
  • Question Question
Adding stock market data from yahoo finance to master Excel file
Replies
3
Views
1K
kcmuppet
K
S
  • Question Question
Matrix Visual in Power BI
Replies
2
Views
185
shansakhi
S

Forum statistics

Threads
1,223,227
Messages
6,170,853
Members
452,361
Latest member
d3ad3y3

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top