How to Filter Card based on filter in Measure

[kilala]

Hi all,

I have 2 measure [Balance] and [Balance LM]

I have visualise these 2 as TABLE and CARD. 

In TABLE, i have following detail only:

In table, I have filtered the visual, where [Balance LM] is not blank & [Balance LM] is not 0.

The output come correctly in table.

However, the figure in CARD for [Balance] is wrong. when i checked, this is because i have invoicenumber in my table. if i remove invoicenumber, i get same figure as CARD.

However, i want the figure to be as per TABLE.

How do I fix this?

Here is my measure for Balance & Balance LM:

Balance =

(VAR enddateLM = [MTD End Date LM]

VAR enddate = [MTD End Date]

RETURN

CALCULATE(

sum(vw_FactARAgeingMovement[InvoiceAmount]),

FILTER(vw_DimDate,vw_DimDate[Date]<=enddate),

vw_DimInvDate, vw_DimInvDate[Date] <= enddateLM)

)

Balance LM = 

VAR enddate = [MTD End Date LM]

RETURN

CALCULATE(

DM_Ageing[Amount],

FILTER(vw_dimdate,vw_DimDate[Date] <= enddate)

)

[HiraNegi]

Hi @kilala ,

Seems like you are trying to find the MTD or YTD values. Can you use TOTALMTD or TOTALYTD function to calculate measure?

Currently in the Balance measure you are trying to filter on 2 dates. What is the purpose of that? Share some more details on the issue to understand it clearly.

Thanks.

[VijayP]

@kilala  If you can show the result in both card and table to understand what exactly happening!

for MTD end and MTD date what measure you have used?

[kilala]

Hi @VijayP @HiraNegi 

I am trying to track the movement of invoice. e.g. how much payment has been made at this month to pay invoice as of last month. 

if today is 31/12/2022:
MTD end date  = 31/12/2022

mtd end date lm = 30/11/2022

I cannot use totalmtd because i need the amount as i'm not calculating from start of the month. I am calculating from start of business. For example, starting 1/1/2015 until 30/11/2022. 

currently the value i'm getting in CARD is higher than TABLE.

E.g: CARD = 190,000

TABLE: 170,000

[kilala]

MTD End Date = CALCULATE(ENDOFMONTH(LASTDATE(vw_DimDate[Date])))

MTD End Date LM = CALCULATE(STARTOFMONTH(LASTDATE(vw_DimDate[Date]))-1)

[HiraNegi]

Hi @kilala ,

Check on below 2 points.

1. Seems your measure expression for Balance is mission FILTER keywork. Please ensure if that is correct.

Balance =

(VAR enddateLM = [MTD End Date LM]

VAR enddate = [MTD End Date]

RETURN

CALCULATE(

sum(vw_FactARAgeingMovement[InvoiceAmount]),

FILTER(vw_DimDate,vw_DimDate[Date]<=enddate),

FILTER(vw_DimInvDate, vw_DimInvDate[Date] <= enddateLM)

))

2. Since the tablaur view is giving you correct data, it is likely that you want the measure to be calculated with Invoive Context. Try to add ALLSELECTED filter in your measure.

Balance =

(VAR enddateLM = [MTD End Date LM]

VAR enddate = [MTD End Date]

RETURN

CALCULATE(

sum(vw_FactARAgeingMovement[InvoiceAmount]),

ALLSELECTED(<TableName>, <InvoiceNumberField>),

FILTER(vw_DimDate,vw_DimDate[Date]<=enddate),

FILTER(vw_DimInvDate, vw_DimInvDate[Date] <= enddateLM)

))

Hope this will help.

[kilala]

hi @HiraNegi ,

I have tried that measure but i am still getting same result as before. I think because i didnt cooperate the filter that i put in table.

In my table, filter I put is:

-invoice number not blank

-Balance LM not blank and not 0.

Any idea to incorporate this into the measure?

Many thanks

[HiraNegi]

Hi @kilala ,

Did you try putting the same filters in card visual as well ?

[Ashish_Mathur]

Hi,

Share some data and show the expected result.

[kilala]

hi,

attached is for your reference

[Ashish_Mathur]

Hi,

Please share the PBI file as well.  Clearly show the problem there.

[kilala]

Hi @Ashish_Mathur ,

Sorry, the data was sensitive to be shared. I tried to create a sample as attached before but it was quite hard to make it same concept as real data.

Anyway, what I'm to achieve is to get [MTD Balance] amount BY INVOICENUMBER.  and filter out those invoicenumber and [mtdbalance] where [mtd balance lm] = blank.

I have tried this before, but kept getting same result as previous data because I cannot filter out [mtd balance] = blank. 

SUMX(

SuMMARIZE(vw_FactARAgeingMovement,vw_FactARAgeingMovement[InvoiceNumber],"InvNo",

CALCULATE(

    sum(vw_FactARAgeingMovement[InvoiceAmount]),

    FILTER(vw_DimDate,vw_DimDate[Date]<=enddate),

    vw_DimInvDate, vw_DimInvDate[Date] <= enddatelm)),[InvNo])

Any idea to filter the measure?

[Ashish_Mathur]

I understand the question best only when i have some data to work with.