Is there a way to embed a measure in to a VAR but be mutable rather than constant

[Steve_Scotland]

Hello all from Scotland, UK

I'll start by saying I presume the answer is 'no'  but if you don't ask.......

~~~~~~~~~~~~~~~~~~~~

I have the example measures below pointing to an imported SQL Table which basically has transaction_date / customer number / Sales Ex_VAT / Profit etc

/// Example measures

Sales Ex-Vat = sum(SQLTable[Sales_Ex_VAT])

Profit = sum(SQLTable[[Profit])

and a Dates table

TY is This Year,  LY is Last Year etc

I then have subsequent measures such as

Retail Sales Week_To_Yesterday_TY =

// Various VAR for Reporting_Week_TY  etc defined

//  Not listing them all here to save space

// ***************************************************

// The Calc bit :

VAR FilteredDates = FILTER( ALL(Dates),

                                 Dates[Week_Int] = Reporting_Week_TY

                                 && Dates[Year Int] = Reporting_Year_TY

                                 && Dates[Stat_Date] >= Monday_ThisWeek_TY

                                && Dates[Stat_Date] <= Yesterday_ThisWeek_TY )

VAR Hold_Calc = SUMX(FilteredDates, [Sales Ex-Vat])

Return IF ( Hold_Calc = BLANK(),0, Hold_Calc)

and

Retail Profit Week_To_Yesterday_TY =

// Various VAR for Reporting_Week_TY  etc defined

//  Not listing them all here to save space

// ***************************************************

// The Calc bit :

VAR FilteredDates = FILTER( ALL(Dates),

                                 Dates[Week_Int] = Reporting_Week_TY

                                 && Dates[Year Int] = Reporting_Year_TY

                                 && Dates[Stat_Date] >= Monday_ThisWeek_TY

                                && Dates[Stat_Date] <= Yesterday_ThisWeek_TY )

VAR Hold_Calc = SUMX(FilteredDates, [Profit] )

Return IF ( Hold_Calc = BLANK(),0, Hold_Calc)

where the only difference between the two is  whether I am calculating Sales Ex-VAT or Profit in the VAR Hold_Calc =

bit

All works tickety boo and I can change the Filtered Dates to be Reporting_Year_LY  etc

The thing is that the "calc bit" is way down in the code

What it occurred to me is  could I  **maybe**   pass a Measure as a variable

ie  put

Required_Measure  = [Sales Ex-VAT]     

(or  Required_Measure = [Profit]  )

at the top of the code

and then do this

/ ***************************************************

// The Calc bit :

VAR FilteredDates = FILTER( ALL(Dates),

                                 Dates[Week_Int] = Reporting_Week_TY

                                 && Dates[Year Int] = Reporting_Year_TY

                                 && Dates[Stat_Date] >= Monday_ThisWeek_TY

                                && Dates[Stat_Date] <= Yesterday_ThisWeek_TY )

VAR Hold_Calc = SUMX(FilteredDates, Required_Measure )

Return IF ( Hold_Calc = BLANK(),0, Hold_Calc)

~~~~~~~~~~

Won't surprise you it didn't work.  Result was Blank()  which converted to zero

It's  a shame as  had it worked I would  subsequently  creating measures for,  for example

Reporting_Week_TY 

and

Reporting_Week_LY

I would have done something like

VAR  Required_Week =  [Reporting_Week_TY]

and made the code super flexible / re-usable  by shoving all the bits in

VAR FilteredDates  = 

up the top and change them depending on requirements

~~~~~~~~~

I've read that  VAR are immutable

Before I abandon the thought process,  I'm writing / asking if there is a work-around or  a different reserved word other than VAR which would allow me to do what I want.

I can't be the only one to have tripped over this

~~~~~~~~~~

A further thought :

Am I better creating measures for

Reporting_Year_TY

Monday_ThisWeek_TY

Yesterday_ThisWeek_TY

etc and referencing them in my Calc_Bit   rather then repeatedly defining them again and again as  
VAR  

in my various new measures

~~~~~~~~~~

As always, thanks in advance

[Sahir_Maharaj]

Hello @Steve_Scotland,

Here are a few approaches you might consider:

1. SWITCH Function: Instead of trying to pass a measure into a variable, you can use a SWITCH function to determine which calculation to perform based on a condition. You can create a parameter table in your model with values like "Sales" and "Profit" and then use a slicer in your report to allow the user to select the desired calculation.
2. Parameterized Measures: While you can't directly pass a measure as a variable, you can create a measure that takes into account different conditions or scenarios. You can use IF or SWITCH to dynamically choose between different calculations.
3. Dynamic Date Ranges: Instead of hardcoding date ranges within each measure, consider creating separate measures or calculated columns for dynamic date ranges like Reporting_Year_TY, Monday_ThisWeek_TY, etc (can then be referenced in your main calculation measures)

Should you have any questions, please do not hesitate to reach out to me.

[Steve_Scotland]

Hello

Thanks for your suggestions.

Most of it went right over my head as I am self taught but  progressing.  Like a lot of people.

I will keep it for future reference.

At the moment, we are not presenting slicers to users.  They use them to create rubbish reports and then complain it is 'wrong'.   The visuals they get at the moment have multiple measures such as a table with columns for Sales This Week,  Profit This Week,  Sales Year to date This Year,  Profit Year to Date Last Year

The rows are then Departments or  Sales Team member  etc etc.

Or a pie chart for one measure (Sales This week, say)  with the slices as Dept or Sales Team member

The way I have structured the measures lends itself to that approach.

Was really pleased had got everything to work.  I was trying to be "clever" and move things on a little in terms of code sustainabiity. Such is life