Forum Discussion
Relative Months from Selected Month
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
Thank you, i will be processing this and testing it and reply with an update by tomorrow.
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,
