Forum Discussion

6 Replies

  • v-zhenbw-msft's avatar
    v-zhenbw-msft
    Icon for Community Support rankCommunity Support
    5 years ago

    Hi mr_oli ,

     

    We can create a table and two measures to meet your requirement.

     

    1. Create a table that contains Forecast week and Forecast week + days.

     

     

    2. And create a relationship between forecast table and stock table.

     

     

    3. Then we can create two measures to get the result.

     

    Total = 
var _max_w_sort = MAX(forecast[Week sort])
var _max_w = CALCULATE(MAX(forecast[week column]),FILTER(forecast,forecast[Week sort]=_max_w_sort))
var _weekadd = CALCULATE(MAX('Table'[Custom]),FILTER('Table','Table'[Forecast]=_max_w))
return
RIGHT(_weekadd,LEN(MAX('Table'[Custom]))-2) &" "& "weeks"

     

    Measure = 
IF(
    ISFILTERED('Table'[Forecast]),
    CALCULATE(SUM(forecast[Forecast]),FILTER(forecast,forecast[week column]=MAX('Table'[Forecast])))+0,
    [Total])

     

     

    The Product 1 on week 04, so the coverage is +7. Don’t know our understanding is correct.

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

     

    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.

     

    BTW, pbix as attached.

    • mr_oli's avatar
      mr_oli
      Icon for Helper I rankHelper I
      5 years ago

      Thank you very much for you answer but I have one question.

       

      Why for product 1 coverage is showed as 7 weeks? Sum of forecast is 9 so we still will have 1 on stock

      • v-zhenbw-msft's avatar
        v-zhenbw-msft
        Icon for Community Support rankCommunity Support
        5 years ago

        Hi mr_oli ,

         

        Sorry for that we have a little confused.

        In your screenshots, why the product id 1 coverages more than 13 weeks. It stopped in forecast wk + 7. We think the coverage will be 7.

        Or you want to get the total forecast? In Product id 1, it will show 2+2+5 = 9, in Product id 2, it will show 3+5+2+2 = 12.

         

         

        Could you please explain your desire result screenshot?

         

        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.

  • v-zhenbw-msft's avatar
    v-zhenbw-msft
    Icon for Community Support rankCommunity Support
    5 years ago

    Hi mr_oli ,

     

    If you want to display the total in each row, we can create a measure to meet your requirement.

     

    Measure = 
IF(
    ISFILTERED('Table'[Forecast]),
    CALCULATE(SUM(forecast[Forecast]),FILTER(forecast,forecast[week column]=MAX('Table'[Forecast])))+0,
    CALCULATE(SUM(forecast[Forecast])))

     

     

    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.

     

    BTW, pbix as attached.

  • v-zhenbw-msft's avatar
    v-zhenbw-msft
    Icon for Community Support rankCommunity Support
    5 years ago

    Hi mr_oli ,

     

    How about the result after you follow the suggestions mentioned in my original post?

    Could you please provide more details or expected result about it If it doesn't meet your requirement?

    If the above posts help, please kindly mark it as a solution to help others find it more quickly.

     

    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.