2 filters for different dimensions in expression (Inventory ending balance in each quarter)

[staceyzhang]

Hello,

I am trying to draw a line chart which could show inventory ending balance for each quarter. 

My data table like this: monthly trial balance for each account (Inventory is the account code starts with "1-14*"). 

I want to set expression to detect the last month of each quarter and also sum of all related inventory account as ending balance. Ex. it will show Q1 as Mar. inventory ending balance to represent.

However, my expression could not go though:

sum({$<AccountCode={"1-14*"}> + QuarterEnd([Year-Quarter])}[TB.EndingBalance])

Please suggest!

Thank you so much!

Stacey

[sidhiq91]

@staceyzhang  Please see the below explanation. 

Quarterend() function gives the result in the form of 03/31/2022. 

You need to modify your set expression a little bit:

Sum({<AccountCode={"1-14*"},[TB.ReportDate]={"=$(=Quarterend(Today()))"}>}[TB.EndingBalance])

If this resolves your issue, please like and accept it as your solution.

[staceyzhang]

Thank you for your response.

I tried this on, but it still shows sum of 3 months balance (Jan-Mar) together as Q1. I am not quite sure, but I want to get each quarter could detect and show the last month's balance. 

Q1: showing March ending balance (sum of  AccountCode={"1-14*"} from [TB.EndingBalance]

Q2: showing June ending balance 

Q3: showing Sept ending balance

Q4: showing Dec ending balance

Thank you .

[sidhiq91]

@staceyzhang with the expression that I have provided should give only Quarter end results.  Since the issue still continues could you please provide your expression and sample data?

[staceyzhang · Accepted solution]

I tried another way and it had been fixed ^^.

In script, I created a new column to get TB.Month. In Chart expression, I put "Sum(if((left(AccountCode,4)= '1-14') and ([TB.Month]= month(QuarterEnd([DA.ReportDate-TB.ReportDate]))),[TB.EndingBalance]))".

Thank you for your help!