Get sum of quantity for last selected date

[PowerBITestingG]

I have a bar chart with date and a sum of quantity. Next to it I have a table with Countries and "Dynamic" sum of quantity.

The idea is that when people click on the dates in the bar chart, the table next to it shows those values, and if nothing is selected then it defaults to showing the last date data.

Dynamic sum of quantity
VAR maxx2 =
    MAXX (
        FILTER (
            ALLSELECTED (fact),
            fact[snapshotdate]
        ),
        fact[snapshotdate])
VAR __Result=
    CALCULATE ( sum(quantity),  fact[snapshotdate] = maxx2 )
return __Result

This produces the desired result except when the following happens:

- If today a country doesnt have any rows on the table and someone uses a filter to show only that country then the measure returns the last date with data (previous day) which creates confusion because people would expect the table to show nothing in that case.

Any ideas on how to fix it?

[Anonymous · Accepted solution]

Hi @PowerBITestingG ,

I created a sample pbix file(see the attachment), please check if that is what you want.

Dynamic sum of quantity = 
VAR _country = SELECTEDVALUE ( 'fact'[country] )
VAR maxx2 =
    MAXX (
        FILTER (
            ALLSELECTED ( 'fact' ),
            'fact'[country] = _country
        ),
        'fact'[snapshotdate]
    )
VAR __Result =
    CALCULATE (
        SUM ( 'fact'[quantity] ),
        FILTER (
            ALLSELECTED ( 'fact' ),
            'fact'[country] = _country
                && 'fact'[snapshotdate] = maxx2
        )
    )
RETURN
    IF ( ISFILTERED ( 'fact'[snapshotdate] ), SUM ( 'fact'[quantity] ), __Result )

If the above one can't help you get the expected result, please provide some raw data in your tables (exclude sensitive data) with Text format and your expected result with backend logic and special examples. It would be helpful to find out the solution. You can refer the following link to share the required info:

How to provide sample data in the Power BI Forum

And It is better if you can share a simplified pbix file. You can refer the following link to upload the file to the community. Thank you.

How to upload PBI in Community

Best Regards

[lbendlin]

Please provide sample data (with sensitive information removed) that covers your issue or question completely, in a usable format (not as a screenshot). Leave out anything not related to the issue.
If you are unsure how to do that please refer to https://community.fabric.microsoft.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-...
Please show the expected outcome based on the sample data you provided.

If you want to get answers faster please refer to https://community.fabric.microsoft.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447...