Forum Discussion

PowerBITestingG's avatar
PowerBITestingG
Icon for Resolver I rankResolver I
3 years ago
Solved

Calculate quantity for max date in snapshot table

I am trying to calculate the total quantity for the most recent date (12-Mar-23)

I am using:

Calculate(Quantity,snapshotdate = max(snapshot date)

I have a snapshot table like this:

 

Snapshot DateCountryQuantity 
10-Mar-2023USA100 
10-Mar-2023USA100 
12-Mar-2023CANADA500 

 

And if I place the measure in the matrix this is what I would exepct:

 

CountryQuantity
USA0
Canada

500

 

But I am getting the latest value for USA for 10-Mar-23, instead of nothing since there is no data for the last date.

 

Using this formula solves the proble, but does anybody know how problematic can allselected be in this case?

test =
VAR maxx2 =
    MAXX (
        FILTER (
            ALLSELECTED ( table ),
            snapshot date
        ),
        snapshot date
    )
RETURN
    CALCULATE ( quantity, Snapshot date = maxx2 )

Any ideas are appreciated!

  • Hi,

     

    try this:

     

    test =
    VAR maxx2 =
        MAXX (
            FILTER (
                ALLSELECTED ( 'Table (7)' ),
                'Table (7)'[Snapshot Date]
            ),
            'Table (7)'[Snapshot Date]
        )
    RETURN
        if(ISBLANK(CALCULATE ( sum('Table (7)'[Quantity]),'Table (7)'[Snapshot Date] = maxx2 )),0,CALCULATE ( sum('Table (7)'[Quantity]),'Table (7)'[Snapshot Date] = maxx2 ))
     

    If I answered your question, please mark my post as solution, Appreciate your Kudos 👍

1 Reply

  • DOLEARY85's avatar
    DOLEARY85
    Icon for Resident Rockstar rankResident Rockstar

    Hi,

     

    try this:

     

    test =
    VAR maxx2 =
        MAXX (
            FILTER (
                ALLSELECTED ( 'Table (7)' ),
                'Table (7)'[Snapshot Date]
            ),
            'Table (7)'[Snapshot Date]
        )
    RETURN
        if(ISBLANK(CALCULATE ( sum('Table (7)'[Quantity]),'Table (7)'[Snapshot Date] = maxx2 )),0,CALCULATE ( sum('Table (7)'[Quantity]),'Table (7)'[Snapshot Date] = maxx2 ))
     

    If I answered your question, please mark my post as solution, Appreciate your Kudos 👍