How to use filter in two related tables?

[ryan_mayu]

Hi , i have two tables. One is dimension date. The other one is fact table. Two tables have already created one to many relationship.

I want to get the Inventory amount of the last month. Can anyone help me fix the DAX below? Then if I choose Jan,Feb and Mar, the inventory will be 300.

Inventory = calculate(sum('fact'[Amount]),'fact'[TYPE]="Inventory"&&LASTDATE(RELATED('Dim_date'[date]))

[v-frfei-msft · Accepted solution]

Hi @ryan_mayu,

Based on my test, I made one sample for your reference here.

1. Enter the data as you shared and create relationship between tables as below.

2. Create a calculated column in Dim_date table.

Month no = MONTH('Dim_date'[Date])

3.Create a calculated column in the fact table.

monno = RELATED(Dim_date[Month no])

4. Create a measure to achieve your goal.

Measure = calculate(sum('FACT'[Amount]),FILTER(ALLSELECTED('FACT'),'fact'[TYPE]="Inventory"&&  'FACT'[monno] = CALCULATE(MAX('FACT'[monno]),ALLSELECTED('FACT'))))

For more details, please check the pbix as attached.

Regards,

Frank

[ryan_mayu]

@v-frfei-msft

Hi , one more futher request for this.

If the data is

customer    Type          Month    Amount  

AAA           Inventory     Jan         100

AAA           Inventory     Feb        150

AAA           Inventory     Mar         120

BBB           Inventory      Jan         110

BBB           Inventory     Feb        100

BBB          Inventory       Mar         100

CCC          Inventory       Jan         200

CCC           Inventory     Feb         160

CCC           Inventory     Mar        100

If I used the DAX formula you provided, In the report view, the result is all the customer's inventory amount is the same, like below. Each customer will show the summary amount of last month.

Customer  Amount

AAA        320

BBB         320

CCC        320

How to modify the DAX formula to make sure we assign the correct amount to each customer?

Thanks in advanced.

[v-frfei-msft]

Hi @ryan_mayu,

Please update the formula as below.

Measure =
CALCULATE (
    SUM ( 'FACT'[Amount] ),
    FILTER (
        'FACT',
        'FACT'[Type] = "Inventory"
            && 'FACT'[MOn]
                = CALCULATE ( MAX ( 'FACT'[MOn] ), ALLEXCEPT ( 'FACT', 'FACT'[customer] ) )
    )
)

Regards,

Frank

[ryan_mayu]

@v-frfei-msft

It works. Thanks a lot.

[Greg_Deckler]

Your formulas aren't jiving with the images you show. Please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490

That being said, See if my Time Intelligence the Hard Way provides a different way of accomplishing what you are going for.

https://community.powerbi.com/t5/Quick-Measures-Gallery/Time-Intelligence-quot-The-Hard-Way-quot-TIT...

I'm still not sure what you are going for, you want Jan, Feb and March to all show 300?

[ryan_mayu]

Hi Greg, Thank for your reply. For inventory data, what we want to focus is the latest data. Inventory data can't be accumulated. That's why when we choose Jan, Feb, March, the inventory data will only show the data for Mar. The same logic to whole data. If I choose twelve month, I want the inventory measure show the Dec's inventory data. That's why I use lastdate formula.

Thanks a lot