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 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)
)
Need Help

12 Replies

  • VijayP's avatar
    VijayP
    Icon for Community Champion rankCommunity Champion
    3 years ago

    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'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 ,

    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.