Forum Discussion

orangeatom's avatar
orangeatom
Resolver I
8 years ago

Relative Months from Selected Month

I require some assistance on creating a column based on a value. In my date dimension i have a column that is named "relative months from current month" which is essentially and integer substracted by 1 from the current month:

 

January 2018; 0

December 2017: -1

November 2017: -2.. so on

 

I need to create this same type of column however using a filter where the user can select the month. The new column would be called "relative months from selected month".

Filter [December 2017]

January 2018; 0

December 2017: 0

November 2017: -1.. so on

 

I have tried a few things but cannot seem to figure it out. I thought i should create a column which contains the selectedvalue however it doesn't appear to capture the selected value:

Selected_Month= FORMAT(SELECTEDVALUE(DimDateInvoice[CalendarMonthNumber])&"/1/"&(MAX(DimDateInvoice[CalendarYearNumber])),"Short Date")

Then use the date diff function to get relative months:

Relativemonthsfromselectedmonth=IFERROR(
DATEDIFF(DimDateInvoice[Month],DimDateInvoice[Selected_Month],MONTH),
(DATEDIFF(DimDateInvoice[Selected_Month],DimDateInvoice[Month],Month))*-1
)

 

Thank you!

Need Help

5 Replies

  • v-ljerr-msft's avatar
    v-ljerr-msft
    Microsoft Employee
    8 years ago

    Hi orangeatom,

     

    If I understand you correctly, you can firstly use the formula below to create a new calculate column in the DimDateInvoice table.

    YearMonth =
DimDateInvoice[CalendarYearNumber] * 12
    + DimDateInvoice[CalendarMonthNumber]

    Then you should be able to use the formula below to create a new measure(not a column) for "relative months from selected month". :smileyhappy:

    Relativemonthsfromselectedmonth =
VAR currentSelectedMonth =
    MAX ( DimDateInvoice[YearMonth] )
RETURN
    IF (
        DimDateInvoice[YearMonth] >= currentSelectedMonth,
        0,
        DimDateInvoice[YearMonth] - currentSelectedMonth
    )

     

    Regards

    • orangeatom's avatar
      orangeatom
      Resolver I
      8 years ago

      Thank you, i will be processing this and testing it and reply with an update by tomorrow.

    • orangeatom's avatar
      orangeatom
      Resolver I
      8 years ago

      Hi v-ljerr-msft,

       

      I aplogize for my delay, I have attempted to implement the solution and in the measure i get an error "a single value for column YearMonth cannot be determined.  ..without aggregation..."

       

      Perhaps this explanation would be better.

       

      In the DimDate table I have the following columns:

      Note: the column [RelativeMonthFromThisMonth] is currently populated from the datawarehouse and the value is based on the extraction month.

       

      Month, RelativeMonthFromThisMonth, 

      02/01/2018,1

      01/01/2018,0

      12/01/2017,-1

      11/01/2017,-2

       

      I would like to create a new colum in PowerBI and based on the user selection of a month (example December 2017) have the DimDate table look like this:

      Month, RelativeMonthFromThisMonth,RelativeMonthFromThisMonthDynamic

      02/01/2018,1,2

      01/01/2018,0,1

      12/01/2017,-1,0

      11/01/2017,-2,-1

       

      I then can use that value to calculate measures in the report. I am not sure how to go about adding that column.

       

      In addition I have the solution complete but its not dynamic (Year and Month as numbers are filters for users), example

       

       

      //When i specify the month in the code it works.

      z_ActiveMonth = FORMAT("12/1/2017","Short Date")

       

      z_RealtiveMonthsFromThisMonthDynamic =
      IFERROR(
      DATEDIFF(DimDateInvoice[MonthFull],DimDateInvoice[z_ActiveMonth],MONTH),
      (DATEDIFF(DimDateInvoice[z_ActiveMonth],DimDateInvoice[MonthFull],Month))*1
      )

       

      //When i try to get the selected value function i see this in the column (/1/) instead of (12/1/2018) (Month and Year being a selectable filter for users as numbers)

      z_ActiveMonth = FORMAT(SELECTEDVALUE(DimDateInvoice[CalendarMonthNumber])&"/1/"&SELECTEDVALUE(DimDateInvoice[CalendarYearNumber]),"Short Date")

       

      the z_RealtiveMonthsFromThisMonthDynamic shows as #ERROR

       

      I thought this would work but it doesn't.

       

      z_ActiveMonth = FORMAT(FORMAT(SELECTEDVALUE(DimDateInvoice[CalendarMonthNumber]),"String")&"/1/"&FORMAT(SELECTEDVALUE(DimDateInvoice[CalendarYearNumber]),"String"),"Short Date")

       

      Thank you,