Forum Discussion

jdwalker5's avatar
jdwalker5
Helper II
2 years ago
Solved

Calculation Based on Selected Value

I'm trying to create a measure that will return a rolling 4 week calculation based on the week the user selects from a slicer, but it is not returning the correct value.  It's only returning the value for the one week that is selected.  Below is the syntax I used(I'm using epochs in the lookup function to account for year change).

ROLLING_4_WK_LOS =
VAR BEGIN_WK =   
          LOOKUPVALUE(
                   'DATA'[Week Epoch],
                   'DATA'[Week to be selected],
                   SELECTEDVALUE(
                            'DATA'[Week to be selected])
) -3
VAR END_WK =    
          LOOKUPVALUE(
                   'DATA'[Week Epoch],
                   'DATA'[Week to be selected],
                   SELECTEDVALUE(
                            'DATA'[Week to be selected])
)
VAR CALC =
    CALCULATE(
        SUM(
               'DATA'[Count of item]),
        'DATA'[Week Epoch] >= BEGIN_WK && 'DATA'[Week Epoch] <= END_WK
 )
RETURN CALC
 
I checked the BEGIN_WK and END_WK variables and they are both returning the correct epochs.
 
Here's the really frustrating part.  The 'Week to be selected' column is text and is what I intend to use as the filter that users will interact with.  This measure works just fine if I use the 'Week Epoch' column as my filter instead of 'Week to be selected'.  However, my users don't know what epoch is and would prefer being able to use the text column as a filter and have the values change accordingly.
 
Sample Data:
Week to be selectedWeek EpochCount of item
Current Week648175128
Report Week6480141910
124066479142453
124056478143106
124046477140394
124036476141213
124026475144977
124016474142189
123526473142372

 

Need Help
  • jdwalker5's avatar
    jdwalker5
    2 years ago

    Thanks Anonymous.

     

    I ended up doing a workaround that uses a chiclet slicer, but what you provided would likely work.  What I did was use a normal slicer for the week then added a chiclet slicer for the epoch.  The chiclet slicer filters when a week is selected AND the value it filters to is a forced selection.  This forced selection is the key to why the workaround works.  I then turned off the interaction between my charts/tables and the week filter, and kept it on for the chiclet slicer.

     

    The users don't need to see the chiclet slicer, so I hid it behind one of the charts.  From their perspective, it looks like the week they're selecting is changing the values, which is what I wanted.

     

    One other thing, the "Slicer (new)" option native to PBI has an option to force selection, but I could not get it to work.  The chiclet slicer works just fine, though.

     

    Thanks!

2 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    Hi jdwalker5 ,

    1. Create a computed table to be used as a slicer and do not create a relationship with the original table.

    Table =
DISTINCT('DATA'[Week to be selected])

    2. If you are trying to display the values of the item for the first 4 weeks of the selected week, then you can modify the expression as follows.

    ROLLING_4_WK_LOS =
VAR BEGIN_WK =
LOOKUPVALUE(
'DATA'[Week Epoch],
'DATA'[Week to be Selected],
SELECTEDVALUE(
'Table'[Week to be selected])
) -3
VAR END_WK =
LOOKUPVALUE(
SELECTEDVALUE(
'Table'[Week to be selected])
)
VAR CALC =
CALCULATE(
SUM(
'DATA'[Count of item]), 'DATA'[Week Epoch] >= BEGIN_WK && 'DATA'[Week Epoch])
'DATA'[Week Epoch] >= BEGIN_WK && 'DATA'[Week Epoch] <= END_WK
)
RETURN CALC

     

    3. If you are trying to ask for the sum of the items in the first four weeks of the selected week,  change the fields in the table to fields in the calculation table.

    Your calculations do not match the expected results because the slicer is the same field as the visual object to be displayed, and the slicer is what affects its visualization. For more detailed information, refer to the documentation: Slicers in Power BI - Power BI | Microsoft Learn.

     

    If your Current Period does not refer to this, please clarify in a follow-up reply.

     

    Best Regards,

    Clara Gong

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

    • jdwalker5's avatar
      jdwalker5
      Helper II
      2 years ago

      Thanks Anonymous.

       

      I ended up doing a workaround that uses a chiclet slicer, but what you provided would likely work.  What I did was use a normal slicer for the week then added a chiclet slicer for the epoch.  The chiclet slicer filters when a week is selected AND the value it filters to is a forced selection.  This forced selection is the key to why the workaround works.  I then turned off the interaction between my charts/tables and the week filter, and kept it on for the chiclet slicer.

       

      The users don't need to see the chiclet slicer, so I hid it behind one of the charts.  From their perspective, it looks like the week they're selecting is changing the values, which is what I wanted.

       

      One other thing, the "Slicer (new)" option native to PBI has an option to force selection, but I could not get it to work.  The chiclet slicer works just fine, though.

       

      Thanks!