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.

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

       

      • Anonymous's avatar
        Anonymous
        Not applicable
        6 years ago

        jdbuchanan71 
        may i know what value this is referring to? I got an error for this.

        [Value Amount]