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

Hello Anonymous 

It's not really clear how you got the date range Jan 2019 to Jun 2019?  Did the user select Jan 2019 AND 6 from the number table?  Meaning the want 6 months of data starting in Jan 2019?  If so, something like the attached should work for you although I did have to add an actual date field to the first table so I could use DATESBETWEEN.

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 

hi, for Jan 2019 to Jun 2019, the value are in the data source, which i want to sum for these 6 months, and the user will select "6" from numbertable, because this is the value for 6 months & later divide it by 12.

I tried the formula but the value look different. would you advice?

Thank you.

  • 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?