Forum Discussion

kilala's avatar
kilala
Icon for Resolver I rankResolver I
3 years ago

How to Filter Card based on filter in Measure

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 visua...
Need Help
kilala's avatar
kilala
Icon for Resolver I rankResolver I
3 years ago

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's avatar
kilala
Icon for Resolver I rankResolver I
3 years ago
MTD End Date = CALCULATE(ENDOFMONTH(LASTDATE(vw_DimDate[Date])))
MTD End Date LM = CALCULATE(STARTOFMONTH(LASTDATE(vw_DimDate[Date]))-1)
  • HiraNegi's avatar
    HiraNegi
    Icon for Resolver II rankResolver II
    3 years ago

    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's avatar
      kilala
      Icon for Resolver I rankResolver I
      3 years ago

      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

       

      • Ashish_Mathur's avatar
        Ashish_Mathur
        Icon for Super User rankSuper User
        3 years ago

        Hi,

        Share some data and show the expected result.