Dynamic calculation based on date slicer

[Anonymous]

I would like to perform a calculation which involves getting value from previous date. Also if the user selects a date value, I want to dynamically set the first value in that table to be set to a base formula.

Formula is as follows.

For the first date value my formula should as follows: 1*(1Value)

For subsequent dates it should = Previous_value*(1+Value)

Here is a sample dataset

category	region	Year	Month	Date	Value
Apples	West	2018	January	January 2018	0.09
Apples	West	2018	February	February 2018	0.09
Apples	West	2018	March	March 2018	0.06
Apples	West	2018	April	April 2018	0.08
Apples	West	2018	May	May 2018	0.21
Apples	West	2018	June	June 2018	0.09
Apples	West	2018	July	July 2018	0.23
Apples	West	2018	August	August 2018	0.01
Apples	West	2018	September	September 2018	0.04
Apples	West	2018	October	October 2018	0.2
Apples	West	2018	November	November 2018	0.01
Apples	West	2018	December	December 2018	0.14
Apples	West	2019	January	January 2019	0.24
Apples	West	2019	February	February 2019	0.08
Apples	West	2019	March	March 2019	0.04
Apples	West	2019	April	April 2019	0.18
Apples	West	2019	May	May 2019	0.04
Apples	West	2019	June	June 2019	0.08
Apples	West	2019	July	July 2019	0.23
Apples	West	2019	August	August 2019	0.21
Apples	West	2019	September	September 2019	0.25
Apples	West	2019	October	October 2019	0.25
Apples	West	2019	November	November 2019	0.05
Apples	West	2019	December	December 2019	0.25

If the user selects the start date as March 2019 using slicers. My desired output should be as follows

• March 2019 the output column = 1*(1+0.04) = 1.04
• April 2019 the output column =   1.04(from march output) *(1+0.18) = 1.23
• May 2019 = 1.23(from April 2019) * (1+0.04) = 1.28

Then table output should be as follows

category	region	Year	Month	Date	Value	Output
Apples	West	2019	March	March 2019	0.04	1.04
Apples	West	2019	April	April 2019	0.18	1.23
Apples	West	2019	May	May 2019	0.04	1.28
Apples	West	2019	June	June 2019	0.08	1.38
Apples	West	2019	July	July 2019	0.23	1.70
Apples	West	2019	August	August 2019	0.21	2.05
Apples	West	2019	September	September 2019	0.25	2.56
Apples	West	2019	October	October 2019	0.25	3.21
Apples	West	2019	November	November 2019	0.05	3.37
Apples	West	2019	December	December 2019	0.25	4.21

Here are the measure that I tried to. It is unable to identify the previous month data

get_minimum_date =

VAR Minimun_Date =

CALCULATE ( min('date'[Date]) )

Return

VAR Date_to_Compare =

CALCULATE(MIN ( 'date'[Date] ),ALLSELECTED())

RETURN

var this_month_data = CALCULATE(Sum(data_v3[Value]))

RETURN

var value_returned = 

IF (

    Date_to_Compare = Minimun_Date,

    0, this_month_data

)

return

value_returned

Using the get_minimum_date measure I tried to do the following measure

prvious_date_cal = CALCULATE([get_minimum_date], PREVIOUSMONTH('date'[Date]))

However my previous month function is not working and is causing errors

Thanks in advance

[lbendlin]

Have you used PRODUCTX() before?  It sounds like the perfect candidate for what you are trying to achieve.

"Custom" is a Date column that is defined in Power Query and is used for date sorting.

 #"Added Custom" = Table.AddColumn(Source, "Custom", each "1-" & [Date]),
 #"Changed Type" = Table.TransformColumnTypes(#"Added Custom",{{"Custom", type date}, {"Value", type number}})

You can apply date range filters, replace the SELECTEDVALUE() with MAX(), change the ALLSELECTED() to ALLEXCEPT() etc. depending on your needs.

[Anonymous]

Thank you for your response. I tried the formula, it does work but I also need the ability to filter by the Postal Code. The Postal code is in a separate table with the region. I have a code table for the region which links to both the original table and the table with Postal Code. As soon as I add the Postal Code as a filter, the measure no longer works, it calculates the wrong value. The Postal Code is not part of the original table. 

region	postal code
west	A1B 2C3
west	D4E 5F6
east	G7H 8I9

[lbendlin]

show your data model

[Anonymous]

Thank you very much this worked!

[Anonymous]

Sorry here is my data model. in the postal code lookup, there are many postal codes per region. 

[lbendlin · Accepted solution]

just add the region filter. (I messed with your sample data as it only had West region)