Forum Discussion

yybi123's avatar
yybi123
Icon for Helper I rankHelper I
6 years ago

Latest Value from another table based on Date Slicer

Hi, I have two tables of raw data.

 

Table 1 - Cost

Cost MonthSKUCost Value
Jan 2020A1
Jan 2020B1.5
Feb 2020A2

 

Table 2 - Sales

Sales MonthSKU
Jan 2020A
Jan 2020B
Feb 2020A
Feb 2020B

 

I will have a Date slicer based on month, what I want to achieve is as per below,

 

When Jan 2020 selected.

Sales MonthSKUCost Value
Jan 2020A1
Jan 2020B

1.5

 

When Feb 2020 selected.

Sales MonthSKUCost Value
Feb 2020A2
Feb 2020B

1.5

 

The cost value will follow as per latest month value if data is available, if it is not available it will go the last data point it has.

Need Help

3 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    6 years ago

    Hi yybi123 ,

    You can get the desired result by completing the following steps:

    1. Create one dimension table SKU

    Create dimension table SKU

    2. Create the relationship between these three tables with field "SKU"

    Create relationship

    3. Create one measure with the following formula and drag the fields(field Sales Month in table Sales, field SKU in table SKU and new created measure New Cost Value) onto table visual just like the one in below screen shot:

    New Cost Value = 
VAR curMonth =
    MAX ( 'Sales'[Sales Month] )
VAR curSKU =
    MAX ( 'Sales'[SKU] )
VAR costValue_1 =
    CALCULATE (
        MAX ( 'Cost'[Cost Value] ),
        FILTER ( 'Cost', 'Cost'[Cost Month] = curMonth && 'Cost'[SKU] = curSKU )
    )
RETURN
    IF (
        ISBLANK ( costValue_1 ),
        CALCULATE (
            MAX ( 'Cost'[Cost Value] ),
            FILTER ( 'Cost', 'Cost'[SKU] = curSKU )
        ),
        costValue_1
    )

    Create measure and visual

    Best Regards

    Rena

    • yybi123's avatar
      yybi123
      Icon for Helper I rankHelper I
      6 years ago

      Hi Rena Anonymous , I managed to the result for my sample data. Will try to replicate it to my real dataset.

      Therefore, I do not really need a Date table in this case? 

       

      Another question, what if I needed to calculate the difference of the New Cost calculated for any two months selected. Using the same example below, I need to calculate the difference between if selected Jan and Feb (user could select any 2 months they like).

       

      Jan sales SKU A cost = 1
      Feb sales SKU A cost = 2

      I need the difference 2-1 = 1

       

      or
      Jan Sales SKU A cost = 1
      March Sales SKU A cost = 3

       

      Difference 3-1 = 2