apply parameter only on range of dates in table

[Learning_BI345]

Dear all,

I am struggling with an analysis that I think should be possible. I have a table with a "date" column (2024 - 2026) a "volume_X" column and a "volume_Y" column. I want to do some analyses where I switch volume amounts between "volume_X" and "volume_Y". In order to do so, I set up three parameters (ranging from -1000 till 1000 with step 100). Each parameter should exchange a certain volume between "volume_X" and "volume_Y" based on the selected parameter value for the a certain year (i.e. parameter 1 exchanges volumes expected in 2024, ..., paramter 3 exchanges volumes expected in 2026). 

For example for 2024 I created first a measures to only show the volumes for X and Y for 2024:

volumes_X_2024 = calculate(SUM(volume_X), Table[date] <= date(2024, 12, 31))

volumes_Y_2024 = calculate(SUM(volume_Y), Table[date] <= date(2024, 12, 31))

The problem I face is that when I select a value like +500 (shocked_volume_shift_2024  = 500) using parameter 1 then the +500 is applied on the whole range (2024-2026) and not only on 2024 volumes. So volume_X is increased with 500 over the whole period while volume_Y is decreased with 500 over the whole period. I tried to resolve this by having a binary series where 1s are shown for dates < 2025 and otherwise zero:

binary_series = IF(SUM(Table[Date]) <= DATE(2024,12,31),1,0)

The binary series is then multiplied with the volume shift to only have +500 where date <= 31-12-2024. The measure is:

Result = ROUND(base_volume_shift_2024 + shocked_volume_shift_2024 * [binary_series], 0).
Plotting the "Result" measure shows what I want, but when I want to have a card visual then I only see a 0. 

Does anyone know how to apply a parameter measure on only a subset of another measure (for example based on a date condition) such that I can apply my scenarios without overlapping?

Thank you so much for the time and effort looking into this.

Best,

Marc

[Learning_BI345]

Dear all, 

In addition to the below description and input data table hopefully the below screenshot provides some clarification:

Problem:
The measure shocked_volume_X_shift is first of all not responsive to any date filters applied in the above figure. Also, in the card visual a value of zero is shown eventhough the figure clearly shows that the measure does have a non-zero value at some dates in 2024 (no filter is applied to the card visual). I would expect that the card visual would have a value of 350.

shocked_volume_X_shift = exchange_volume_parameter_2024[exchange_volume_parameter_2024 Value] * [2024_binary_series]

2024_binary_series = IF(SUM(Input_Table[year_date]) = 2024,1,0)

Hope anyone could help clarify the issue and ideally provide a solution. Please let me know whether anything is unclear.

Best,

Marc

[lbendlin]

Do you want to apply your parameter slicer to the Volume or to the base shift?  Are the slicer values applied to X and the inverse to Y, or the other way round?

[Learning_BI345]

Hi Ibendlin,

I want to apply the shocks directly to the volume (the cumulative sum of the shock amount X for example for 2024 is added to the volume X). Indeed the slicer values in your image will be the shock values for X and the the inverses to Y (i.e. we exchange volume X for volume Y).

Do let me know whether you need further clarification.

Best,

Marc

[lbendlin]

Note that I had to fix the Y axis scale to prevent it from looking weird.

[Learning_BI345]

Thank you for your quick response. Indeed this gives the desired result for the line-graph, but I wish to be able to show in a card visual the shock amount that is added in total for example for 2024 volume X. For example inserting the measure "volumex" in the card visual makes the shown number non-responsive when I adjust the slider values.

Do you perhaps know how to fix this? Also is the proposed solution easily scalable if I have for example multiply volume categories (i.e. x, y and z)?

Thank you

[lbendlin]

I don't see what the expected result would be for the card visual? "volumex"  only makes sense for individual dates.

If you have more than two categories then how do you plan to distribute the over/under?

[Learning_BI345]

Given the measure

volumex = sum('Table'[Volume_X])+SWITCH(YEAR(SELECTEDVALUE('Table'[Date])),

2024,[2024 Value],

2025,[2025 Value],

2026,[2026 Value])

If I want to present for example the base volume X delta for 2024 then I can simply create a card visual with the "Base_shift_Volume_X" as input and a filter to only show dates with year 2024. How can I create a similar card for the shocked delta amount that is sensitive to the selected value for 2024 slider?

Thank you.

Best,

Marc

[lbendlin]

If I want to present for example the base volume X delta for 2024 then I can simply create a card visual with the "Base_shift_Volume_X" as input and a filter to only show dates with year 2024. 

You need some sort of aggregation to put that value in a card visual.

[Learning_BI345]

Hi all,

The below table is used to illustrate the problem I am having. This table is imported as Input_Table, where I add a year_date column ( YEAR(Input_Table[Date]) ). Then I set up a numeric range parameter, for example for 2024:

exchange_volume_parameter_2024 = GENERATESERIES(-100, 100, 10).
The user should be able to assign a value to this parameter via a slicer/slider.


With this numeric parameter I only want to shock the volume shifts for 2024. Therefore I first create a measure 

base_shift_volume_x_2024 = CALCULATE(SUM(Input_Table[Base_shift_Volume_X]), Input_Table[year_date] = 2024)

Then I create a Results table with a binary series for 2024. This series shows a 1 when date is in 2024 in order to apply the shock parameter from base_shift_volume_x_2024  on dates within 2024:

binary_series_2024 = IF(SUM(Input_Table[year_date]) = 2024,1,0)

finally the shocked volume shift is defined as:

shocked_shift_volume_X_2024 = exchange_volume_parameter_2024[exchange_volume_parameter_2024 Value] * [binary_series_2024]

The problem I face is that when I visualise shocked_shift_volume_X_2024 in a line chart then I observe non-zero values in 2024 and zeroes in 2025 and 2026. However in a card visual I observe a zero when inserting shocked_shift_volume_X_2024 and all dates occuring in 2024 are selected while I would expect to see a value equal to remainin number of months in 2024 * selected parameter value (exchange_volume_parameter_2024)

Date	Volume_X	Volume_Y	Base_shift_Volume_X	Base_shift_Volume_Y
30/06/2024	5000	11000	100	720
31/07/2024	5100	11720	40	460
31/08/2024	5140	12180	20	720
30/09/2024	5160	12900	100	600
31/10/2024	5260	13500	80	360
30/11/2024	5340	13860	20	160
31/12/2024	5360	14020	40	720
31/01/2025	5400	14740	80	320
28/02/2025	5480	15060	40	60
31/03/2025	5520	15120	100	840
30/04/2025	5620	15960	100	640
31/05/2025	5720	16600	80	620
30/06/2025	5800	17220	40	780
31/07/2025	5840	18000	40	800
31/08/2025	5880	18800	60	40
30/09/2025	5940	18840	20	980
31/10/2025	5960	19820	100	500
30/11/2025	6060	20320	60	20
31/12/2025	6120	20340	80	840
31/01/2026	6200	21180	80	20
28/02/2026	6280	21200	100	700
31/03/2026	6380	21900	0	0
30/04/2026	6380	21900	100	340
31/05/2026	6480	22240	80	480
30/06/2026	6560	22720	100	220
31/07/2026	6660	22940	20	780
31/08/2026	6680	23720	100	700
30/09/2026	6780	24420	20	840
31/10/2026	6800	25260	0	220
30/11/2026	6800	25480	40	700
31/12/2026	6840	26180	20	20

[Learning_BI345]

Hi Ibendlin, please find below the input data (Input_Table). As an example to demonstrate the issue I face, I created the following measures/colums/tables (for 2024 only):

• In the Input_Table I added a column year_date ( year_date = YEAR(Input_Table[Date]) ) 
• A numeric range parameter for 2024 called exchange_volume_parameter_2024 ( exchange_volume_parameter_2024 = GENERATESERIES(-100, 100, 10) ) This numeric range parameter is used within a slicer to select the shocked volume amount via a slider.
  
  
• A results table (Results = SELECTCOLUMNS('Input_Table',[Date]) and witihn this table I created two measures:
  • binary_series_2024 = IF(SUM(Input_Table[year_date]) = 2024,1,0)
  • shocked_shift_volume_X_2024 = exchange_volume_parameter_2024[exchange_volume_parameter_2024 Value] * [binary_series_2024]

If I select for example exchange_volume_parameter_2024 Value = 10 (via the slicer) then a line chart of shocked_shift_volume_X_2024  shows a shock amount only for 2024 but when I create a card visual with  shocked_shift_volume_X_2024  then the shown value is zero. If in this card I add the Date column of Results as filter and only select one date (i.e. 31-7-2024) then I get a value 10, but if I would select all dates in 2024 I get a value of zero while I would expect a value 70 (number of remaining months in 2024 * 10).

Date	Volume_X	Volume_Y	Base_shift_Volume_X	Base_shift_Volume_Y
30/06/2024	5000	11000	100	720
31/07/2024	5100	11720	40	460
31/08/2024	5140	12180	20	720
30/09/2024	5160	12900	100	600
31/10/2024	5260	13500	80	360
30/11/2024	5340	13860	20	160
31/12/2024	5360	14020	40	720
31/01/2025	5400	14740	80	320
28/02/2025	5480	15060	40	60
31/03/2025	5520	15120	100	840
30/04/2025	5620	15960	100	640
31/05/2025	5720	16600	80	620
30/06/2025	5800	17220	40	780
31/07/2025	5840	18000	40	800
31/08/2025	5880	18800	60	40
30/09/2025	5940	18840	20	980
31/10/2025	5960	19820	100	500
30/11/2025	6060	20320	60	20
31/12/2025	6120	20340	80	840
31/01/2026	6200	21180	80	20
28/02/2026	6280	21200	100	700
31/03/2026	6380	21900	0	0
30/04/2026	6380	21900	100	340
31/05/2026	6480	22240	80	480
30/06/2026	6560	22720	100	220
31/07/2026	6660	22940	20	780
31/08/2026	6680	23720	100	700
30/09/2026	6780	24420	20	840
31/10/2026	6800	25260	0	220
30/11/2026	6800	25480	40	700
31/12/2026	6840	26180	20	20

[lbendlin]

Please provide sample data that covers your issue or question completely, in a usable format (not as a screenshot).

Do not include sensitive information or anything not related to the issue or question.

If you are unsure how to upload data please refer to https://community.fabric.microsoft.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-...

Please show the expected outcome based on the sample data you provided.

Want faster answers? https://community.fabric.microsoft.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447...