cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
slav84
Frequent Visitor

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?

1 ACCEPTED SOLUTION
sayaliredij
Solution Supplier
Solution Supplier

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

View solution in original post

3 REPLIES 3
yingyinr
Community Support
Community Support

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

Community Support Team _ Rena
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
sayaliredij
Solution Supplier
Solution Supplier

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

View solution in original post

tchristy99
Helper II
Helper II

Hi,

 

To declare a variable you would simply write the following:

 

VAR variableName = "Variable Value"

 

RETURN

 

variableName //The calculation you want to return

Helpful resources

Announcements
Power BI October Update 2021.jpg

Power BI Release

Click here to read more about the October 2021 Release!

Microsoft Ignite 768x460.png

Find your focus

Explore the latest tools,training sessions,technical expertise, networking and more.

Power BI Womens Summit 2021 768 x460.jpg

Interviews, learning sessions, allies, and more!

#PowerBIWomenSummit

Teds Dev Camp Oct. 2021 768x460.jpg

Power BI Dev Camp - October 28th, 2021

Mark your calendars and join us for our next Power BI Dev Camp!