topic Re: Dynamic calculation based on date slicer in DAX Commands and Tips

Dynamic calculation based on date slicer

Anonymous — Tue, 18 Aug 2020 20:29:04 GMT

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

Re: Dynamic calculation based on date slicer

lbendlin — Tue, 18 Aug 2020 21:38:13 GMT

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.

Re: Dynamic calculation based on date slicer

Anonymous — Wed, 19 Aug 2020 16:01:39 GMT

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

Re: Dynamic calculation based on date slicer

lbendlin — Wed, 19 Aug 2020 16:44:27 GMT

show your data model

Re: Dynamic calculation based on date slicer

Anonymous — Wed, 19 Aug 2020 19:02:35 GMT

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

Re: Dynamic calculation based on date slicer

lbendlin — Wed, 19 Aug 2020 19:25:47 GMT

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

Re: Dynamic calculation based on date slicer

Anonymous — Tue, 25 Aug 2020 17:37:44 GMT

Thank you very much this worked!