Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

Dynamic calculation based on date slicer

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's avatar
    lbendlin
    6 years ago

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

     

     

6 Replies

  • 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's avatar
      Anonymous
      Not applicable

      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. 

       

      regionpostal code
      westA1B 2C3
      westD4E 5F6
      eastG7H 8I9