Always return result from latest month regardless if there is any data

[Yuiitsu]

Hi All

I have this visual that show the outstanding USD invoice for current month. 

I have filter it such that it will always automatically show the latest report month. There isnt any issue until this month, because there is no outstanding for USD however it still shows a value.

Upon checking I realised because this month is blank for USD, it shows the outstanding for last month instead

Can anyone give me recommandation how can I resolve it? I want it to show as (blank) for USD because this month there should be no outstanding for USD.

[Jihwan_Kim]

Hi, I am not sure how your [Latest Report Month] looks like, but please try to write the measure that removes currency filter.

REMOVEFILTERS function (DAX) - DAX | Microsoft Learn

[Yuiitsu]

Hi

My report month column are date column.

Sorry I dont understand why remove currency filter? I need the amount to show based on currency filter though...

[Jihwan_Kim]

Hi,

Thank you for your message.

I like to suggest inserting a measure into it, instead of inserting the column directly.

And, I think, a measure can be written with having REMOVEFILTERS DAX function.

Thank you.

[Yuiitsu]

Hi

Thank you for the quick reply.

But I dont understand what you mean by inserting a measure into it?

Is it possible for you to show me an example?

[Jihwan_Kim]

Hi,

Please share your sample pbix file, and then I can try to show step by step process.
Thank you.

[Yuiitsu]

Sorry I cannot upload pbix file here neither can I share box link as well because it is a company box account.

Is it good enough if i share a sample table?

Report Month	Customer Name	Product	Curr	Total Balanced Amount
2024-02-27	AAA	Rubber	JPY	100000
2024-02-27	AAA	Rubber	JPY	355554
2024-01-20	AAA	Gear	JPY	178187
2024-01-20	AAA	Washer	USD	378037
2024-01-20	AAA	Wheel	USD	3900
2024-01-20	AAA	Wheel	JPY	192651
2023-12-15	AAA	Gear	JPY	223500
2023-12-15	AAA	Wheel	JPY	29000
2023-12-15	AAA	Washer	USD	324000
2024-02-27	BBB	Wheel	JPY	227424
2024-02-27	BBB	Washer	USD	1404288
2024-02-27	BBB	Nic	JPY	3661600
2024-02-27	BBB	Rubber	SGD	3900
2024-01-20	BBB	Gear	JPY	600
2024-01-20	BBB	Rubber	USD	1200
2024-01-20	BBB	Rubber	JPY	3600
2024-01-20	BBB	Gear	SGD	2700
2023-12-15	BBB	Nic	JPY	25400
2023-12-15	BBB	Gear	SGD	10800
2023-12-15	BBB	Washer	USD	162000

Here is a sample of how the visual looks like.

So by default it should always show the latest month's value (in this sample it would be February 24), if that month has no value for that currency, it should show blank.

But there should be a date slicer at the side in the event user wish to see other older months.

Also there should be a customer slicer too.

[Jihwan_Kim · Accepted solution]

Hi,

Thank you for your message.

I tried to recreate your sample like below.

Please check the below picture and the attached pbix file whether it suits your requirement.

Total balanced amount: = 
IF ( HASONEVALUE ( 'Currency'[Currency] ), SUM ( Data[Balanced Amount] ) )

Latest yearmonth amount: = 
VAR _latestyearmonth =
    LASTNONBLANK (
        'Calendar'[Year-Month sort],
        CALCULATE (
            SUM ( Data[Balanced Amount] ),
            REMOVEFILTERS ( 'Currency'[Currency] )
        )
    )
VAR _result =
    CALCULATE (
        [Total balanced amount:],
        'Calendar'[Year-Month sort] = _latestyearmonth
    )
RETURN
    _result

[Yuiitsu]

Thank you!

Did a few random checks and it seems to run nicely!