Summarizing data from two different table

DAX DAX Calculations
1

Hi,

I have two tabels:

'Items' with [ItemId] and 'Sale' with [ItemId], [SalesQty] and [SalesDate]. Relationship between tables is created via [ItemId]

I would like to create one summarized tabel with columns: [ItemId] , SUM(SalesQty), LastDate(SalesDate).

How I can do that?
Thanks

2

Vladas,

I’m new to this but thought I would take a stab. I loaded some sample data into Power BI (see Item Data and Sales Data in the visual). I then was able to drag the fields into a table and select Latest for the date column and sum for the SalesQty column in Power BI on the Fields pane of the table visual.

image
image.png808×589 26.6 KB

image

image

I also was able to do the same by creating a measure for the latest date and sum of SalesQTY

SumSalesQty = sum(Sales[SaleQty])
MaxDate = max(Sales[SalesDate])

The same result is displayed in the visual “Using Measures for Sum SalesQty and Date”.

I don’t know if this helps but I thought I would try.

Thanks!
Dawn

3

Nice one, looks good Dawn.

I would personally always default to DAX measures for everything, that’s how I like to do things.

Chrs
Sam

4

True. Since taking many of the courses, that is my default too.

:grin:

5

@dethompson97 - that’s good workaround.

But for me it’s important to create a measure, because I will do further calculations with this data.

I tried to do :

ItemsLastSalesDate =
SUMMARIZE (
    Items,
    Items[ItemID],
    "Total SalesQty", SUM ( Sale[SalesQty] ),
    "LatestDate", LASTDATE ( Sale[SalesDate] )
)

but I get an error "The syntax for ‘Item’ is incorrect. Here ‘Items’ - lookup tabel and ‘Sale’ - fact tabel.

6

This by itself can’t be a measure as SUMMARIZE is a table function.

To get the last sale date of an item all you should need is LASTDATE( Sale[SalesDate] ) or you could use MAX.

Any reason you couldn’t just use something similar to this?

7

Thanks for help. It little bit strange but I just changed realtionships and everything works.