dax formula

[Anonymous]

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.

[jdbuchanan71]

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]

@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]

@Anonymous 

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

[Anonymous]

@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]

@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]

@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]

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]

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

[Value Amount]

[jdbuchanan71]

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

[Anonymous]

@jdbuchanan71 
need your help again.
I tried the forumla but it doesnt seem to give me the correct value.

For example if i select Product A, Jan-Aug, total sales = 445

if user selected number '8', the answer should be 667.5 instead of the following?

[jdbuchanan71]

@Anonymous 

Please share your .pbix file, the one you took the screen shot from so I can see how you have the model and report set up.

[Anonymous]

Here it is
@jdbuchanan71 

[jdbuchanan71 · Accepted solution]

@Anonymous 

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

[Anonymous]

Thank you so much!It worked.

@jdbuchanan71