powerbidax

In Power BI, there is a multi-select cycle filter. I have get the max cycle value. Question 1. how to the previous cycle of the max cycle

![User can single select or multi-select,I'd like to get the max cycle,which is 25-CY04]]1 enter image description here

I create below measure:

_MAX_SELECTED_CYCLE = IF(
    ISFILTERED('MCCP Cycle Dimension'[FISCAL_YEAR_CYCLE_SHORT]),
     maxx(FILTERS('MCCP Cycle Dimension'[FISCAL_YEAR_CYCLE_SHORT]),'MCCP Cycle Dimension'[FISCAL_YEAR_CYCLE_SHORT])
  )

the _MAX_SELECTED_CYCLE looks correct. it is 25-CY04. enter image description here

Now, I want to get the cycle prior to 25-CY04, which is 25-CY03.I create a measure :

_PREVIOUS_CYCLE = 
  CALCULATE(
    MAX('MCCP Cycle Dimension'[FISCAL_YEAR_CYCLE_SHORT]),
    FILTER(
      'MCCP Cycle Dimension',
      'MCCP Cycle Dimension'[FISCAL_YEAR_CYCLE_SHORT] <[_MAX_SELECTED_CYCLE]
    )
  )

why the _PREVIOUS_CYCLE is always blank? how to correctly get it? enter image description here

Second issue,I create another measure

_ENGAGEMENT_OF_THE_MAX_SELECTED_CYCLE = 
  CALCULATE(
    SUM('Activity Fact'[ENGAGEMENTS]),
    FILTER(
      'MCCP Cycle Dimension',
      'MCCP Cycle Dimension'[FISCAL_YEAR_CYCLE_SHORT]=[_MAX_SELECTED_CYCLE]
    )
  )

,just want to get the ENGAGEMENT_OF_THE_MAX_SELECTED_CYCLE, the engagement of 25-CY04. MY Second issue here, when user only single select, the ENGAGEMENT_OF_THE_MAX_SELECTED_CYCLE is correct. enter image description here

But when user multiple select, say 25-CY04,and 25-CY03.The ENGAGEMENT_OF_THE_MAX_SELECTED_CYCLE become the sum of 25-CY04,25-CY03. enter image description here But my requirement is to get the ENGAGEMENT_OF_THE_MAX_SELECTED_CYCLE. And I would like to compare ENGAGEMENT_OF_THE_MAX_SELECTED_CYCLE (25-CY04) with ENGAGEMENT_OF_THE_PREVIOUS_CYCLE(25-CY03). And eventually use an up or down arrow indicator to show the change. Thanks for the help in advance

This is table 'MCCP Cycle Dimension' enter image description here

this is table 'Activity Fact' enter image description here

Solution

  • The issue you have is that you have your measure within the FILTER function, and as a result, this measure will be calculated for each row context within the FILTER. Move your measure outside the FILTER in a var and then use that in your FILTER:

    _PREVIOUS_CYCLE = 
  // measure is calculated here, outside of the filter
  var cy = [_MAX_SELECTED_CYCLE]
  return
    CALCULATE(
      MAX('MCCP Cycle Dimension'[FISCAL_YEAR_CYCLE_SHORT]),
      FILTER(
        'MCCP Cycle Dimension',
        'MCCP Cycle Dimension'[FISCAL_YEAR_CYCLE_SHORT] < cy
      )
    )

    The following includes ALLSELECTED as an optional enhancement:

    _MAX_SELECTED_CYCLE = 
  CALCULATE(
    IF(
      ISFILTERED('MCCP Cycle Dimension'[FISCAL_YEAR_CYCLE_SHORT]),
      MAX('MCCP Cycle Dimension'[FISCAL_YEAR_CYCLE_SHORT]) 
    ),
    ALLSELECTED('MCCP Cycle Dimension'[FISCAL_YEAR_CYCLE_SHORT])
  )


_PREVIOUS_CYCLE =
  var cy = [_MAX_SELECTED_CYCLE]
  return
    CALCULATE(
      MAX('MCCP Cycle Dimension'[FISCAL_YEAR_CYCLE_SHORT]),
      'MCCP Cycle Dimension'[FISCAL_YEAR_CYCLE_SHORT] < cy,
      ALLSELECTED('MCCP Cycle Dimension'[FISCAL_YEAR_CYCLE_SHORT])
    )


_ENGAGEMENT_OF_THE_MAX_SELECTED_CYCLE = 
  var cy = [_MAX_SELECTED_CYCLE]
  return
    CALCULATE(
      SUM('Activity Fact'[ENGAGEMENTS]),
      'MCCP Cycle Dimension'[FISCAL_YEAR_CYCLE_SHORT] = cy
    )


_ENGAGEMENT_OF_THE_PREV_SELECTED_CYCLE = 
  var cy = [_PREVIOUS_CYCLE]
  return
    CALCULATE(
      SUM('Activity Fact'[ENGAGEMENTS]),
      'MCCP Cycle Dimension'[FISCAL_YEAR_CYCLE_SHORT] = cy
    )