Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago

look up value for a date using YEAR

Hey

 

I have a table with users:

UserPeriodAccountExtra1
user12020-01-01FullA
user12021-01-01FullB
user22020-01-01MobileB
user22021-01-01FullB

EDIT: changed user table to clarify that User is a text and not number field.

 

 

And a table with account costs:

CategorySubCatCostYear
AccountFull2002020
AccountFull2202021
AccountMobile1002020
AccountMobile1102021
Extra1A502020
Extra1A602021
Extra1B1002020
Extra1B1102021


Now I know how to lookup the cost if there is just one value - but how do I match the year as well? 
Can it be done in a measure or do I need to make a column in the User-table? 

And can I make a Matrix that basically is something like this (with a date-slide selected for sometime in 2020 in the example below)

IDAccount Extra Total
 FullMobileAB 
1200 50 250
2 100 100200



Need Help

2 Replies

  • v-zhenbw-msft's avatar
    v-zhenbw-msft
    Community Support
    6 years ago

    Hi Anonymous ,

     

    We can add an ID column in table with account costs to meet you requirement.

     

    1. Create a year column in table with users.

     

    Year = YEAR('Table'[Period])

     

     

    2. Then we can create an ID column.

     

    ID = 
var x = CALCULATE(SUM('Table'[User]),FILTER('Table','Table'[Account]='Table (2)'[SubCat] && 'Table'[Year]='Table (2)'[Year] ))
var y = CALCULATE(SUM('Table'[User]),FILTER('Table','Table'[Extra1]='Table (2)'[SubCat] && 'Table'[Year]='Table (2)'[Year]))
return
IF(ISBLANK(x),y,x)

     

     

    3. At last we can create a matrix visual, and filter the ID is 1 and 2. The result like this,

     

     

    If it doesn’t meet your requirement, could you please show the exact expected result based on the table that you have shared?

    BTW, pbix as attached.

     

    Best regards,

     

    Community Support Team _ zhenbw

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

    • Anonymous's avatar
      Anonymous
      Not applicable
      6 years ago

      Hi v-zhenbw-msft 

       

      my bad for just using 1 and 2 as users, the user field is a text field and not a number. 

       

      I think my primary concern is how to look up the year in the value table, and using the Category to find the right cost value. 

      I'm leaning towards splitting the cost table into seperate tables which are each linked to the main USer-table and running lookups to each seperate table.

       

      But still need to understand how to check the year 😄