Forum Discussion
How to use variable while creating table?
Hi,
I am having problem while trying to create new MonthlyData summary table.
I am using the function below which gives me what I want however I would have to manualy update the value 18 every week to correct week. Since we are in fiscal week 18 i want to get summary by month using every fiscal week before 18th. Next week this would change to 19.
MonthlyData =
ADDCOLUMNS(
SUMMARIZE(WeeklyData, WeeklyData[FiscalMonth]),
"LYNetRevenue",CALCULATE(SUM(WeeklyData[LYNetRevenue]), WeeklyData[FiscalWeek] < 18),
"TYNetRevenue",CALCULATE(SUM(WeeklyData[TYNetRevenue]), WeeklyData[FiscalWeek] < 18))
I tried creating Measure like:
CurrentFW = CALCULATE(MAX([FiscalWeek]), FILTER(ALL('Epiphany TimeTable'),'Epiphany TimeTable'[Date] =TODAY()))
which gives me current fiscal week number (18) however when i try to replace it within the first function it gives me an error:
"A function 'CALCULATE' has been used in a True/False expression that is used as a table filter expression. This is not allowed."
The work around would be to create variable CurrentFW instead of measure but how do I implement the variable while creating this table?
I tried creating Measure like:
CurrentFW = CALCULATE(MAX([FiscalWeek]), FILTER(ALL('Epiphany TimeTable'),'Epiphany TimeTable'[Date] =TODAY()))
which gives me current fiscal week number (18) however when i try to replace it within the first function it gives me an error:
"A function 'CALCULATE' has been used in a True/False expression that is used as a table filter expression. This is not allowed."
The work around would be to create variable CurrentFW instead of measure but how do I implement the variable while creating this table?
You can try following DAX Code
MonthlyData =
var CurrentFW = CALCULATE(MAX([FiscalWeek]), FILTER(ALL('Epiphany TimeTable'),'Epiphany TimeTable'[Date] =TODAY()))
var result =
ADDCOLUMNS(
SUMMARIZE(WeeklyData, WeeklyData[FiscalMonth]),
"LYNetRevenue",CALCULATE(SUM(WeeklyData[LYNetRevenue]), WeeklyData[FiscalWeek] < CurrentFW ),
"TYNetRevenue",CALCULATE(SUM(WeeklyData[TYNetRevenue]), WeeklyData[FiscalWeek] < CurrentFW ))
RETURN
resultRegards,
Sayali
3 Replies
Hi,
To declare a variable you would simply write the following:
VAR variableName = "Variable Value"
RETURN
variableName //The calculation you want to return
You can try following DAX Code
MonthlyData =
var CurrentFW = CALCULATE(MAX([FiscalWeek]), FILTER(ALL('Epiphany TimeTable'),'Epiphany TimeTable'[Date] =TODAY()))
var result =
ADDCOLUMNS(
SUMMARIZE(WeeklyData, WeeklyData[FiscalMonth]),
"LYNetRevenue",CALCULATE(SUM(WeeklyData[LYNetRevenue]), WeeklyData[FiscalWeek] < CurrentFW ),
"TYNetRevenue",CALCULATE(SUM(WeeklyData[TYNetRevenue]), WeeklyData[FiscalWeek] < CurrentFW ))
RETURN
resultRegards,
Sayali
