Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

dax formula

i have a the following data 1) numbertable below (numbertable) 2) a column of MMYY (date) 3) column with all the value for MMYY (values) In my scenario, User select an option for MMYY and nu...
Need Help
  • jdbuchanan71's avatar
    jdbuchanan71
    6 years ago

    Anonymous 

    Thank you, I modified the file a bit (added added a date table and updated the measure) and attached it.

jdbuchanan71's avatar
jdbuchanan71
Super User
6 years ago

Anonymous 

Can you share your .pbix file and and example of the expected results?

Anonymous's avatar
Anonymous
Not applicable
6 years ago

jdbuchanan71 

I have 3 years data, 2017,2018 & 2019 (Jan to Aug figure)
I am trying to achieve an extrapolated value for a period of 12 months

This will give me an extrapolated value from Jan to Dec 2019.


I am able to do it in excel but was wondering if it make sense to achieve in power bi.
Thank you.

  • jdbuchanan71's avatar
    jdbuchanan71
    Super User
    6 years ago

    Anonymous 

    To project the amount for 12 months in 2019 for product A wouldn't you want to sum Jan - Aug then divide by 8 the multiply by 12?

     

                        total / 8 * 12
    Product Jan-19 Feb-19 Mar-19 Apr-19 May-19 Jun-19 Jul-19 Aug-19 Total Projected
    Product A 23 60 30 99 21 94 55 63 445 667.5
    Product B 19 14 71 56 36 25 77 74 372 558
    Product C 64 84 24 32 61 36 96 5 402 603
    Product 1 30 30 99 21 94 55 63 83 475 712.5
    Product 2 71 71 56 36 25 77 74 23 433 649.5
    Product 3 24 5 15 52 82 57 74 23 332 498
    Product 4 99 83 60 2 1 65 74 23 407 610.5
    Product 5 56 23 91 95 72 66 94 55 552 828
    Product 6 30 24 24 71 56 36 25 77 343 514.5
    Product 7 71 99 99 99 99 99 99 99 764 1146
    Product 8 24 56 56 56 56 56 56 56 416 624
    Product 9 99 99 99 99 99 99 99 99 792 1188
    Product 10 56 56 56 56 56 56 56 56 448 672

     

    How is the number table used?

    • Anonymous's avatar
      Anonymous
      Not applicable
      6 years ago

      jdbuchanan71 

      One of my criteria is that different product has a start date.
      So the no. of months to divide is different

      is it possible?

      • jdbuchanan71's avatar
        jdbuchanan71
        Super User
        6 years ago

        We just need to modify the measure a bit to do the correct caluculation.

        Value in range = 
VAR StartDate = FIRSTDATE('Table'[Date])
VAR SelectedNumber = MAX ( SELECTEDVALUE ( Numbers[Number] ) -1, 0)
VAR EndDate = DATEADD(StartDate,SelectedNumber,MONTH)
RETURN 
CALCULATE(
    [Value Amount], ALL ( 'Table' ), DATESBETWEEN( 'Table'[Date],StartDate,EndDate) ) / SelectedNumber * 12