Forum Discussion
How to Filter Card based on filter in Measure
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?
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
- MTD End Date = CALCULATE(ENDOFMONTH(LASTDATE(vw_DimDate[Date])))MTD End Date LM = CALCULATE(STARTOFMONTH(LASTDATE(vw_DimDate[Date]))-1)
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]RETURNCALCULATE(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]RETURNCALCULATE(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.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
