Forum Discussion

Frequent Visitor

10 years ago

Solved

Variable in calculate Function

Hi everybody,

I'm a new user of Power BI and would like tu use variables in functions but i don't understand how it works.

I would like to use this function :

Part Privé N =
CALCULATE(

SUM([Solde]);[Exercice]=30/06/2016)

I would like to replace '30/06/2016' by a variable in order to change it easily.

Also, Il would like to use this same variable for other functions.

Could you please help me ?

Thank you

v-haibl-msft
10 years ago
Jcho10

You can use variables as following formulas. Here [Latest_Exercice] is a measure which always returns the latest date. You can also use other measures which returns a value.
This is just a sample and we need to adjust the formulas according to the actual requirement.

Measure 1:
Latest_Exercice = MAX ( Table1[Exercice] )

Measure 2:
Part Privé N = VAR Exercice = [Latest_Exercice] RETURN ( CALCULATE ( SUM ( [Solde] ), Table1[Exercice] = Exercice ) )

Best Regards,
Herbert

3 Replies

v-haibl-msft
Microsoft Employee
10 years ago
Jcho10

You can use variables as following formulas. Here [Latest_Exercice] is a measure which always returns the latest date. You can also use other measures which returns a value.
This is just a sample and we need to adjust the formulas according to the actual requirement.

Measure 1:
Latest_Exercice = MAX ( Table1[Exercice] )

Measure 2:
Part Privé N = VAR Exercice = [Latest_Exercice] RETURN ( CALCULATE ( SUM ( [Solde] ), Table1[Exercice] = Exercice ) )

Best Regards,
Herbert
- Jcho10
  Frequent Visitor
  10 years ago
  HI?
  
  Thank you for your solution. It works well !
  
  Sincerely
mohan_g_das1986
Helper III
7 years ago
Hi,
I am trying to calculate the YoY, QoQ & MoM using your Dax variable formula, but i am stuck at applying filters at calculated function

LastYR_WK = MAX(Timm[Week LY])
ST_WK_Filter =
Var Wk_No = [LastYR_WK]
RETURN
( CALCULATE(SUM(W12017[ST]),W12017[Week_Ref] = Wk_No ))

Table - W12017
Week_Ref ST Date
2017W01 20,998 10/31/2016 0:00
2016W01 18,939 11/2/2015 0:00

Table - TIme

Date Date LY Week Week LY
10/31/2016 0:00 11/2/2015 0:00 2017W01 2016W01
11/7/2016 0:00 11/9/2015 0:00 2017W02 2016W02
10/30/2017 0:00 10/31/2016 0:00 2018W01 2017W01
11/6/2017 0:00 11/7/2016 0:00 2018W02 2017W02