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 se...
Need Help
  • Anonymous's avatar
    Anonymous
    4 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.

     

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
4 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.

 

  • Anonymous's avatar
    Anonymous
    Not applicable
    4 years ago

    I solved my problem by your help.