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 number table (example: Jan 2019)
the results will be sum of all values from Jan 2019 to Jun 2019 (existing data source) * numbertable / 12

is it possible to write a dax on the above? Thank you.

Need Help

14 Replies

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