Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

Power BI dynamic month selection

 

Hi All,

Looking for help on a logic.

Create a new column which gives AND condition based on selected months.

Basing on my slicer selection it has to pick up values between the selected months and return 1 if we have data(1) for all selected months.

 

Ex If I select Jan to April in the slicer, then it should be give AND condition of Jan to April columns
      If I select Feb to April in the slicer, then it should give AND of Feb to April columns

Later in a card I want to show count of 1's from newly created column.

In the above Ex the slicer is from Jan to Apri, So in the newly created measure/column I should get 1 for the user Aditya.

If the slicer is from Jan to Mar, then for Achintya and Aditya we should get 1. This should be dynamic.

 

Thanks in advance. Please let me know if any other information needed.

 
Need Help
  • v-yuta-msft's avatar
    v-yuta-msft
    6 years ago

    Anonymous ,

     

    Suppose the login-date column is in another calendar table, you can create a measure as below:

    Result = 
VAR Start_Month =
    MONTH ( CALCULATE ( MIN ( 'Calendar'[Date] ), ALLSELECTED ( 'Calendar' ) ) )
VAR End_Month =
    MONTH ( CALCULATE ( MAX ( 'Calendar'[Date] ), ALLSELECTED ( 'Calendar' ) ) )
RETURN
    SWITCH (
        SWITCH (
            Start_Month,
            1, SWITCH (
                End_Month,
                1, MAX ( 'Table'[January] ),
                2, AND ( MAX ( 'Table'[January] ), MAX ( 'Table'[February] ) ),
                3, AND (
                    MAX ( 'Table'[January] ),
                    AND ( MAX ( 'Table'[February] ), MAX ( 'Table'[March] ) )
                ),
                4, AND (
                    AND ( MAX ( 'Table'[January] ), MAX ( 'Table'[February] ) ),
                    AND ( MAX ( 'Table'[March] ), MAX ( 'Table'[April] ) )
                )
            ),
            2, SWITCH (
                End_Month,
                2, MAX ( 'Table'[February] ),
                3, AND ( MAX ( 'Table'[February] ), MAX ( 'Table'[March] ) ),
                4, AND (
                    MAX ( 'Table'[February] ),
                    AND ( MAX ( 'Table'[March] ), MAX ( 'Table'[April] ) )
                )
            ),
            3, SWITCH (
                End_Month,
                3, MAX ( 'Table'[March] ),
                4, AND ( MAX ( 'Table'[March] ), MAX ( 'Table'[April] ) )
            ),
            4, MAX ( 'Table'[April] )
        ),
        TRUE (), 1,
        FALSE (), 0,
        1, 1,
        0, 0
    )

     

    Community Support Team _ Jimmy Tao

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

4 Replies

  • amitchandak's avatar
    amitchandak
    Icon for Super User rankSuper User
    6 years ago

    Anonymous , Do not select month column, use this measure on the top of you measure =Measure

    new Measure=

    if([Measure]+0 >1,1,0)

    • Anonymous's avatar
      Anonymous
      Not applicable
      6 years ago

      Hi Amith,

      But I don't have any measures created here. January, February are my columns.

      • amitchandak's avatar
        amitchandak
        Icon for Super User rankSuper User
        6 years ago

        Anonymous , Does not seem like a right model.

        You can try if(([Jan]+[Feb] +[Mar] +[Apr]) >1,1,0)

         

        Or

        Can you share sample data and sample output.

  • v-yuta-msft's avatar
    v-yuta-msft
    Icon for Community Support rankCommunity Support
    6 years ago

    Anonymous ,

     

    Suppose the login-date column is in another calendar table, you can create a measure as below:

    Result = 
VAR Start_Month =
    MONTH ( CALCULATE ( MIN ( 'Calendar'[Date] ), ALLSELECTED ( 'Calendar' ) ) )
VAR End_Month =
    MONTH ( CALCULATE ( MAX ( 'Calendar'[Date] ), ALLSELECTED ( 'Calendar' ) ) )
RETURN
    SWITCH (
        SWITCH (
            Start_Month,
            1, SWITCH (
                End_Month,
                1, MAX ( 'Table'[January] ),
                2, AND ( MAX ( 'Table'[January] ), MAX ( 'Table'[February] ) ),
                3, AND (
                    MAX ( 'Table'[January] ),
                    AND ( MAX ( 'Table'[February] ), MAX ( 'Table'[March] ) )
                ),
                4, AND (
                    AND ( MAX ( 'Table'[January] ), MAX ( 'Table'[February] ) ),
                    AND ( MAX ( 'Table'[March] ), MAX ( 'Table'[April] ) )
                )
            ),
            2, SWITCH (
                End_Month,
                2, MAX ( 'Table'[February] ),
                3, AND ( MAX ( 'Table'[February] ), MAX ( 'Table'[March] ) ),
                4, AND (
                    MAX ( 'Table'[February] ),
                    AND ( MAX ( 'Table'[March] ), MAX ( 'Table'[April] ) )
                )
            ),
            3, SWITCH (
                End_Month,
                3, MAX ( 'Table'[March] ),
                4, AND ( MAX ( 'Table'[March] ), MAX ( 'Table'[April] ) )
            ),
            4, MAX ( 'Table'[April] )
        ),
        TRUE (), 1,
        FALSE (), 0,
        1, 1,
        0, 0
    )

     

    Community Support Team _ Jimmy Tao

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