How to use variable while creating table?

[slav84]

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?

[v-yiruan-msft]

Hi @slav84 ，

Whether your problem has been resolved? If yes, could you please mark your post as Answered? It will help the others in the community find the solution easily if they face the same problem with yours. Thank you.

Best Regards

[sayaliredij · Accepted solution]

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
result

Regards,

Sayali

[tchristy99]

Hi,

To declare a variable you would simply write the following:

VAR variableName = "Variable Value"

RETURN

variableName //The calculation you want to return