How to assign value for a variable dynamically?

[bon0]

Dear all,

can you please advise with the following?

I need to create report with custom "reporting years".

That means to select start and end date from the slicer - eg. Feb16-Jan18. First Year will be then Feb16-Jan17, Second Year Feb17-Jan18.

I managed to do this based on the calculated column:

MonthNum =
VAR StartMonth = 2
RETURN
IF (
MONTH ('DimDate'[DateVal] ) >= StartMonth,
MONTH ('DimDate'[DateVal] ) - (StartMonth - 1),
12
+ (
MONTH ('DimDate'[DateVal]) - (StartMonth - 1)))

In the code above I am putting  "2" as a number of the month (february) and then recalculating the months order in the date table. Can you please advise how to assign the value dynamically from the slicer? If Im taking e.g.: 

StartMonth = month(MIN('DateDate'[DateVal])) then it is not working, also not succesful with the selectedvalue. do you have any idea? Thanks.

[judspud]

Hi @bon0 

You can use a WhatIf parameter.

This provided a changeable value as a slicer which you can use to change the start month number.

Thanks,

George

[bon0]

Thanks George,

I tried your solution and it is not working. Can you please elaborate more on this topic?

Thanks

Jan

[judspud]

Hi @bon0 

Instead of manually entering 2 for the variable you can assign the whatif parameter value instead.

When you change the whatif parameter using the slicer the variable changes and therefore the outcome changes accordingly.

Feel free to share your pbix file and i will assist where possible 🙂

Thanks,

George

[bon0]

Thanks, can you please advise how the share the pbix file? Can you provide your email?

All I need to do is to insert value from the slicer into the calculated column.

Thanks

[judspud]

Hi @bon0 

You can try creating a shareable link through one drive or via the internet.

Alternatively if you are simply trying to add the parameter to the calculation, please attach a screenshot of the parameter table and columns and i will try to include the correct code in a reply 🙂

Thanks,

George

[bon0]

Parameter(StartMonth) = GENERATESERIES(1, 12, 1)

StartMonth Value = SELECTEDVALUE('StartMonth'[StartMonth], 1)

DateTable=Date = ADDCOLUMNS(
CALENDAR("2015, 01, 01"," 2018, 12, 31"),
"Year", YEAR([Date]),
"Month Year Num", CONCATENATE(YEAR([Date]), FORMAT([Date],"MMM")),
"Month Num", Month([Date]),
"Month", FORMAT([Date], "MMM"),
"Quarter Num",FORMAT([Date], "Q"),
"Quarter", CONCATENATE("Q",FORMAT([Date], "Q")),
"Day", FORMAT([Date],"D"),
"Week", CONCATENATE("Week ",FORMAT([Date], "WW"))
)

Calculated column in the datetable

MonthNum =

VAR StartMonth = [StartMonth Value]

RETURN

IF (

MONTH ('Date'[Date] ) >= StartMonth,

MONTH ('Date'[Date] ) - (StartMonth - 1),

12

+ (

MONTH ('Date'[Date]) - (StartMonth - 1)))

I found in another forum this statement: Calculated column: Will only be re-calculated if the data model is refreshed.

Does that mean that it cannot be assigned dynamically without refreshing? Thanks. Jan

[judspud]

Apologies I missed that in your original question. 

Yes a parameter will only affect a measure dynamically

Thanks,

George