Forum Discussion

ryan_mayu's avatar
ryan_mayu
Icon for Super User rankSuper User
7 years ago
Solved

URGENT! How to filter data in different table?

Hi all,

 

I have three tables: rawdata, date and type. In the rawdata, I added a new column whose name is "New Type". Then I linked the "New Type" column to the "type" column in type table.

 

I want to calculate the last month's amount and the last second month's amount. I know we can use dateadd formula, but that formula does not work when you choose several month and display data as a card.

 

Below is the formula I used:

 

 
 
test =
VAR maxdate=MAX('Rawdata'[date])
VAR PM1 = DATE(YEAR(maxdate),MONTH(maxdate)-1,01)
VAR totalamount=sum(Rawdata[amount])
VAR PM1TOTAL=CALCULATE([totalamount],FILTER(ALLEXCEPT(Rawdata,Rawdata[New Type]),'Rawdata'[date]=PM1))
RETURN
PM1TOTAL
 
Then I can use New Type column to filter data and drag that column to a table view. However, when I use type column to filter, the data is incoorect. 
 
Can anyone help me on that?
 
 
 
Date table:
 
Rawdata table(New Type column was created in PBI,   New Type = SWITCH('Rawdata'[Type],"AAA","New","Old")
 
Type table
 
I got the wrong data when I use type column to filter, (test column should show last month's amount)
 
Thanks a lot
Need Help
  • v-lili6-msft's avatar
    v-lili6-msft
    7 years ago

    hi, ryan_mayu

    Please try this formula,

    test = 
VAR maxdate=MAX('Rawdata'[date])
VAR PM1 = DATE(YEAR(maxdate),MONTH(maxdate)-1,01)
VAR totalamount=sum(Rawdata[amount])
VAR PM1TOTAL=CALCULATE([totalamount],FILTER(ALLEXCEPT(Rawdata,Rawdata[New Type],'Type table'[type]),'Rawdata'[date]=PM1))
RETURN
PM1TOTAL

    In your conditional, ALLEXCEPT(Rawdata,Rawdata[New Type]) , so only Rawdata[New Type] can filter the data.

    you just add a conditional for 'Type table'[type] in ALLEXCEPT(Rawdata,Rawdata[New Type],'Type table'[type])

     

    Result:

     

    Best Regards,

    Lin

     

4 Replies

  • ryan_mayu's avatar
    ryan_mayu
    Icon for Super User rankSuper User
    7 years ago

    Can anyone help me on this? It's really urgent. Thanks in advance

    • v-lili6-msft's avatar
      v-lili6-msft
      Icon for Community Support rankCommunity Support
      7 years ago

      hi, ryan_mayu

      Please try this formula,

      test = 
VAR maxdate=MAX('Rawdata'[date])
VAR PM1 = DATE(YEAR(maxdate),MONTH(maxdate)-1,01)
VAR totalamount=sum(Rawdata[amount])
VAR PM1TOTAL=CALCULATE([totalamount],FILTER(ALLEXCEPT(Rawdata,Rawdata[New Type],'Type table'[type]),'Rawdata'[date]=PM1))
RETURN
PM1TOTAL

      In your conditional, ALLEXCEPT(Rawdata,Rawdata[New Type]) , so only Rawdata[New Type] can filter the data.

      you just add a conditional for 'Type table'[type] in ALLEXCEPT(Rawdata,Rawdata[New Type],'Type table'[type])

       

      Result:

       

      Best Regards,

      Lin

       

      • ryan_mayu's avatar
        ryan_mayu
        Icon for Super User rankSuper User
        7 years ago

        Hi Lin,

         

        It works. Many thanks for your help. Really apprecated that.