CODEWITHSUNDEEP
powerbidax
Nathan Adams
Nathan Adams

Reputation: 1255

DAX - How to sum the most recent values by group?

I have 2 tables, the first is a sanitised version of the data, the second is what I need to calculate.

AnimalCount
Animal  DATE        Count
Lion    2019-01-01  2
Cat     2019-01-01  45
Cat     2019-01-02  40
Fish    2019-01-02  900
Cat     2019-01-03  50
Fish    2019-01-05  800
Fish    2019-01-06  1200
Cat     2019-01-07  45
Lion    2019-01-07  1
Lion    2019-01-08  0



Calculated
DATE (unique)   Sum of latest Count by Animal
2019-01-01      47 = 45 Cats + 2 Lions
2019-01-02      942 = 900 Fish + 40 Cats + 2 Lions
2019-01-03      952 = 50 Cats + 900 Fish + 2 Lions
2019-01-05      852 = 800 Fish + 50 Cats + 2 Lions
2019-01-06      1252 = 1200 Fish + 50 Cats + 2 Lions
2019-01-07      1246 = 1 Lion + 45 Cats + 1200 Fish
2019-01-08      1245 = 45 Cats + 1200 Fish

Here are the queries I've tried so far, both of them give slightly different results to what I'm looking for.

I'd be super grateful for any suggestions?

Query 1.
LatestTotal = SUMX(
    GROUPBY(
        FILTER(
            AnimalCount,
            [DATE] <= EARLIER([DATE])
        ),
        [Animal],
        "LatestGroupTotal",
        SUMX(CURRENTGROUP(), [Count])
    ),
    [LatestGroupTotal]
)

Query 2.
LatestTotal = SUMX(
    ADDCOLUMNS(
        ADDCOLUMNS(
            VALUES(AnimalCount[Animal]),
            "AnimalName",
            [Animal],
            "LastDate",
            CALCULATE(
                MAX(AnimalCount[DATE]),
                NOT(ISBLANK(AnimalCount[Count]))
            )
        ),
        "LastValue",
        SUMX(
            FILTER(
                AnimalCount,
                [AnimalName] = [Animal]
                && [DATE] = [LastDate]
            ),
            [Count]
        )
    ),
    [LastValue]
)

Upvotes: 2

Views: 2382

Answers (2)

RADO
RADO

Reputation: 8148

First, create a measure for total animal count (for further convenience):

Total Animal Count = SUM(AnimalCount[Count])

Then, add this measure:

Latest Total = 
VAR 
   Current_Date = MAX ( AnimalCount[DATE] )
VAR 
   All_Latest_Dates =
    ADDCOLUMNS ( 
        ALL ( AnimalCount[Animal] ),
        "Last Date", CALCULATE ( 
                         MAX ( AnimalCount[DATE] ), 
                         AnimalCount[DATE] <= Current_Date )
    )
VAR Animal_Latest_Dates =
    TREATAS ( All_Latest_Dates, AnimalCount[Animal], AnimalCount[DATE] )
RETURN
    CALCULATE ( [Total Animal Count], Animal_Latest_Dates )

Result:

enter image description here

Explanation:

This formula requires an understanding of "data lineage" concept in DAX:

Understanding Data Lineage in DAX

In short:

  • store date visible in a current context in a variable;
  • then, generate a virtual table ("All_Latest_Dates"), that consists of all animals, and their last known date for each "current" date;
  • next, apply this virtual table to the AnimalCount table using TREATAS (please read the article to understand how it works);
  • finally, calculate animal count using the virtual table as a new filter context.

This approach results in an optimal query execution plan, and the formula should give you good performance even if your data is large.

Upvotes: 2

Alexis Olson
Alexis Olson

Reputation: 40204

How about this?

LatestTotal =
VAR CurrDate =
    MAX ( AnimalCount[DATE] )
VAR Summary =
    SUMMARIZE (
        FILTER ( ALLSELECTED ( AnimalCount ), AnimalCount[DATE] <= CurrDate ),
        AnimalCount[Animal],
        "LastDate", MAX ( AnimalCount[DATE] )
    )
VAR CountAtDate =
    ADDCOLUMNS (
        Summary,
        "Count", LOOKUPVALUE (
            AnimalCount[Count],
            AnimalCount[Animal], [Animal],
            AnimalCount[DATE], [LastDate]
        )
    )
RETURN
    SUMX ( CountAtDate, [Count] )

First, we summarize the table by Animal looking for the last date associated with each one that is less than or equal to the date of the row we're in (CurrDate).

Then we look up the count for that animal on that date and add all those counts together.

Upvotes: 1

Related Questions