Relative Months from Selected Month

orangeatom · ‎01-15-2018

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!

v-ljerr-msft · ‎01-15-2018

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

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

Regards

orangeatom · ‎01-23-2018

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,