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

     

Greg_Deckler's avatar
Greg_Deckler
Icon for Community Champion rankCommunity Champion
3 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
    3 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.

       

      • Anonymous's avatar
        Anonymous
        Not applicable
        3 years ago

        I solved my problem by your help.