See new orders compared to last week

[Anonymous]

Hello guys,

I have to tables of data with open orders by our clients cleaned up and imported to the data model. Both tables have a columns with order file number and the corresponding weight (we're a logistics company) of all the containers comprising the order. I'd like to report on the total weight of new orders this week as compared to the data last week, i.e., find out which order numbers did not appear in last week's table and sum the corresponding weights of these new orders. As i'm pretty new to DAX I do not know how to do this in Power BI. 

Anyone know how to find out the total weight on the new orders based on comparing order file numbers? Greetings

[Anonymous]

Thank you for responding guys, however this is not what i am looking for. I'll try to simplify my question.

• I have two tables
• Both tables each contain a column for [OrderFileNumber].
• Both columns also contain a column for [Order Weight] with corresponding weight for each file number listed in the column for [OrderFileNumber].
• First table has the data for this week, let's call it 'OrdersThisW'. The second table is called 'OrdersLastW'.
• Now, the 'OrdersLastW[OrderFileNumber]' has a whole bunch of of file numbers. The 'OrdersThisW[OrderFileNumber]' has a lot of these file numbers also, but also some new ones (for new orders that came in this week).
• I need to find only the new file numbers in this week's table (i.e., those that do not appear in last week's data) and sum the corresponding weight.
• Any way to do this? It does not have anything to do with time, just with comparing two columns, find out which values (order file numbers) are unique to this week's one and sum the corresponding weight values of these unique (new) file numbers.

Anyone know how to this in an elegant way? Thanks in advance

[Anonymous]

Nobody?

[V-lianl-msft]

Hi @Anonymous ,

In Power query, there is Date.IsInCurrentWeek functin that returns a logical value indicating whether the given Date/DateTime/DateTimeZone occurred during the current week, as determined by the current date and time on the system.

To check if the date is in previous week, there is no such a function to achieve this directly. You need to return the week number and previous week number, and check if the week number=previous week number. We can use Date.WeekOfYear function in Power query to get the week number. Add a custom Column:

= Table.AddColumn(#"YourPreviousStep", "LastWeek", each if Date.IsInPreviousWeek([Date]) then "Yes" else "No")

Finally, you can use DAX to calculate the total weight of a new order

Best Regards,
Liang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous]

Try something like this as a measure:

New Weight = CALCULATE(

SUM('TableName'[WeightColumn]),

DATE(YEAR(NOW()),MONTH(NOW()), DAY(NOW())),

DISTINCT('TableName'[OrderFileNumberColumn]))

Let me know what that gets you.