cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Titatovenaar2
Helper I
Helper I

DAX Filter Context: Show Current Month Value while ignoring RELATED Calendar selection

Hi guys,

 

The idea I want to achieve is pretty simple: I want to show the total Stock (based on a SUM of a column of table 'FACT Inventory') of the current month (2021-07), while it ignores any period selected from the 'DIM Calendar's period selection, which is related 1:* one directional to 'FACT Inventory'.

 

I thought about doing this the following way:

- First remove the entire 'DIM Calendar' filter context, by using ALL('DIM Calendar')

- Then after that, set it dynamically to the latest month, using the following DAX:

 

CALCULATE(
    [Closing Stock],
    ALL('DIM Calendar'[Date]),
    FILTER(
        'Fact Inventory', 
        RELATED('DIM Calendar'[YearMonth]) = FORMAT(TODAY(), "YYYY-MM")
    )
)

 

The Closing Stock measure is really simple:

 CALCULATE(
    SUM('Fact Inventory'[Quantity]),
    'Fact Inventory'[Source] = "OpeningStock"
 )

 

Somehow this still reacts to a period that I select. If I select the entire 2021, I do see only a value at month 2021-07. However, If I select a period from 2021-01 until 2021-06, it won't show me the current month that is 2021-07.

 

Any ideas how to adapt the filter context in a way that it will show me the current month's value?

 

Kind regards,

Igor

 

1 ACCEPTED SOLUTION
m3tr01d
Resolver III
Resolver III

Hello, you should probably use something like :

VAR _Today = FORMAT(TODAY(), "YYYY-MM")
RETURN
CALCULATE(
    [Closing Stock],
    ALL('DIM Calendar'[Date]),
    'DIM Calendar'[YearMonth] = _Today
)

View solution in original post

2 REPLIES 2
Titatovenaar2
Helper I
Helper I

Works perfectly, thanks!

m3tr01d
Resolver III
Resolver III

Hello, you should probably use something like :

VAR _Today = FORMAT(TODAY(), "YYYY-MM")
RETURN
CALCULATE(
    [Closing Stock],
    ALL('DIM Calendar'[Date]),
    'DIM Calendar'[YearMonth] = _Today
)

View solution in original post

Helpful resources

Announcements
UG GA Amplification 768x460.png

Launching new user group features

Learn how to create your own user groups today!

Power BI October Update 2021.jpg

Power BI Release

Click here to read more about the October 2021 Release!

Community Connections 768x460.jpg

Community & How To Videos

Check out the new Power Platform Community Connections gallery!

Teds Dev Camp Oct. 2021 768x460.jpg

Power BI Dev Camp - October 28th, 2021

Mark your calendars and join us for our next Power BI Dev Camp!