Forum Discussion

marcmen's avatar
marcmen
Helper I
4 years ago
Solved

Simple Variable for Static Column Names

Hi all, I am looking for a way to replace static table/column names with variables. I have several tables to create based on the following

 

Amortization PS =

SUMMARIZE(filter(CROSSJOIN(AOPF,'Date'),'Date'[Date]>=(AOPF[Project Start Date]) && 'Date'[Date]<=(AOPF[Consulting/PS Amortization End Date])),

[Year-Month],[Date],[Project Title],

"CapEx Daily",Max(AOPF[Consulting/PS CapEx])/(DATEDIFF(Min(AOPF[Project Start Date]),Max(AOPF[Consulting/PS Amortization End Date]),DAY)+1),

"OpEx Daily",MAX(AOPF[Consulting/PS OpEx])/(DATEDIFF(Min(AOPF[Project Start Date]),

Max(AOPF[Consulting/PS Amortization End Date]),DAY)+1))

 

What I am trying to do is replace items such as “Consulting/PS Amortization End Date” with a variable. Such as

VAR AmEndDate = Consulting/PS Amortization End Date

or

(AOPF[Consulting/PS Amortization End Date])

Nothing I try seems to work unless I use a function but that does not resolve my issue.

Any help would be appreciated

  • marcmen ,

    Like this ?

    Amortization PS =
    var _col = AOPF[Consulting/PS Amortization End Date]
    return
    SUMMARIZE(filter(CROSSJOIN(AOPF,'Date'),'Date'[Date]>=(AOPF[Project Start Date]) && 'Date'[Date]<=(_col)),
    [Year-Month],[Date],[Project Title],
    "CapEx Daily",Max(AOPF[Consulting/PS CapEx])/(DATEDIFF(Min(AOPF[Project Start Date]),Max(AOPF[Consulting/PS Amortization End Date]),DAY)+1),
    "OpEx Daily",MAX(AOPF[Consulting/PS OpEx])/(DATEDIFF(Min(AOPF[Project Start Date]),
    Max(AOPF[Consulting/PS Amortization End Date]),DAY)+1))

     

     

    if variable means selectedvalue in the slicer. Then it will not work  , new table or column do not take slicer value

2 Replies

  • marcmen ,

    Like this ?

    Amortization PS =
    var _col = AOPF[Consulting/PS Amortization End Date]
    return
    SUMMARIZE(filter(CROSSJOIN(AOPF,'Date'),'Date'[Date]>=(AOPF[Project Start Date]) && 'Date'[Date]<=(_col)),
    [Year-Month],[Date],[Project Title],
    "CapEx Daily",Max(AOPF[Consulting/PS CapEx])/(DATEDIFF(Min(AOPF[Project Start Date]),Max(AOPF[Consulting/PS Amortization End Date]),DAY)+1),
    "OpEx Daily",MAX(AOPF[Consulting/PS OpEx])/(DATEDIFF(Min(AOPF[Project Start Date]),
    Max(AOPF[Consulting/PS Amortization End Date]),DAY)+1))

     

     

    if variable means selectedvalue in the slicer. Then it will not work  , new table or column do not take slicer value