CODEWITHSUNDEEP
powerbidaxpowerbi-desktop
R0b0tn1k
R0b0tn1k

Reputation: 4306

PowerBI: Get District max per location and date

I've seen all posts regarding this issue, but none of them work for the issue below.

The table is simple:

Source - Quantity - DateTime
DS01 100 01/10/19 08:00
DS01 90 01/10/19 08:25
DS01 80 01/10/19 08:30
DS02 3000 01/09/19 18:45 
DS02 2000 01/10/19 08:10 
DS02 1800 01/10/19 08:30 
DS02 1200 01/10/19 08:45
DS03 45000 10/09/19 17:30
DS03 30000 10/10/19 12:11  
DS03 10000 11/22/19 17:30

I need to get the value for the most recent DateTime, so the output would be:

DS03 10000 11/22/19 17:30
DS02 1200 01/10/19 08:45
DS01 80 01/10/19 08:30

What's the best way to do that?

Upvotes: 0

Views: 43

Answers (2)

Leo - Database Consultant
Leo - Database Consultant

Reputation: 21

I sorted it out using a different way:

First I found the "Most Recent Date": TableGroup = GROUPBY(YOUR_DATASET,YOUR_DATASET[Source], "Most Recent Date", MAXX(CURRENTGROUP(),YOUR_DATASET[DateTime]))

After I took the related qty to my "Most Recent Date" and data source: Qty = CALCULATE(maxx(YOUR_DATASET,YOUR_DATASET[Quantity]),FILTER(YOUR_DATASET,YOUR_DATASET[Source] = TableGroup[YOUR_DATASET_Source]), FILTER(YOUR_DATASET, YOUR_DATASET[DateTime] = TableGroup[Most Recent Date]))

This is my dataset:

And

this is the resultset:

Upvotes: 0

Olly
Olly

Reputation: 7891

You can create a table with the required output using SUMMARIZECOLUMNS:

Output Table = 
    SUMMARIZECOLUMNS ( 
        MyTable[Source], 
        "Latest Quantity",
        VAR MaxDate = MAX ( MyTable[DateTime] )
        RETURN
            CALCULATE ( 
                VALUES ( MyTable[Quantity] ),
                MyTable[DateTime] = MaxDate
            ),
        "Latest Date",
        MAX ( MyTable[DateTime] )
    )

Download an example PBIX file here: https://pwrbi.com/so_58839916/

Upvotes: 1

Related Questions