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 

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]

       

      • jdbuchanan71's avatar
        jdbuchanan71
        Super User
        6 years ago

        That is the measure that just adds up the value column.  In your first post, item 3.