Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago
Solved

Calculated column for month selection - Current month, previous month, next month

I want to create a calculated column for my date dimension. I have another table where I have information regarding the current period. I have created already month selection as follow:

 

Month selection =
IF (
    DISTINCT ( 'CurrentPeriod'[CurrentPeriod] ) = 'Date'[Period],
    "Current month",
    IF (
        DISTINCT ( 'CurrentPeriod'[CurrentPeriod] )-1 = 'Date'[Period],
        "Previous month",
        BLANK ()
    )
    )

 

The problem is that the second if statement would not work during year change without an extra logic. I don't want to build an extra which check if the period is december or january. Could you please advice a proper solution?

 

 

Need Help
  • Anonymous's avatar
    Anonymous
    3 years ago

    Hi Anonymous ,

     

    I suggest you to try this code to create a calculated column.

    Month selection =
VAR _PERVIOUS_MONTH =
    CALCULATE (
        MAX ( 'Date'[Period] ),
        FILTER (
            ALL ( 'Date' ),
            'Date'[Period] < DISTINCT ( 'CurrentPeriod'[CurrentPeriod] )
        )
    )
RETURN
    IF (
        DISTINCT ( 'CurrentPeriod'[CurrentPeriod] ) = 'Date'[Period],
        "Current month",
        IF ( 'Date'[Period] = _PERVIOUS_MONTH, "Previous month", BLANK () )
    )

    Here I create a sample to have a test. The period in CurrentPeriod table is 202201, so the previous period should be 202112, we couldn't get it by CurrentPeriod -1.

    Result is as below.

     

    Best Regards,
    Rico Zhou

     

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

     

4 Replies

  • Greg_Deckler's avatar
    Greg_Deckler
    Icon for Community Champion rankCommunity Champion
    4 years ago

    Anonymous Sorry, having trouble following, can you post sample data as text and expected output?
    Not really enough information to go on, please first check if your issue is a common issue listed here: https://community.powerbi.com/t5/Community-Blog/Before-You-Post-Read-This/ba-p/1116882

    Also, please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490

    The most important parts are:
    1. Sample data as text, use the table tool in the editing bar
    2. Expected output from sample data
    3. Explanation in words of how to get from 1. to 2.

    • Anonymous's avatar
      Anonymous
      Not applicable
      4 years ago

      Below are pictures the result what I'am looking for

       

       

      Picture from Current period table

       

       

      The relationship is already posted in the first message.

       

       

      • Anonymous's avatar
        Anonymous
        Not applicable
        3 years ago

        Hi Anonymous ,

         

        I suggest you to try this code to create a calculated column.

        Month selection =
VAR _PERVIOUS_MONTH =
    CALCULATE (
        MAX ( 'Date'[Period] ),
        FILTER (
            ALL ( 'Date' ),
            'Date'[Period] < DISTINCT ( 'CurrentPeriod'[CurrentPeriod] )
        )
    )
RETURN
    IF (
        DISTINCT ( 'CurrentPeriod'[CurrentPeriod] ) = 'Date'[Period],
        "Current month",
        IF ( 'Date'[Period] = _PERVIOUS_MONTH, "Previous month", BLANK () )
    )

        Here I create a sample to have a test. The period in CurrentPeriod table is 202201, so the previous period should be 202112, we couldn't get it by CurrentPeriod -1.

        Result is as below.

         

        Best Regards,
        Rico Zhou

         

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