Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now

Reply
slav84
Helper I
Helper I

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
Super User
Super User

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





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




View solution in original post

3 REPLIES 3
v-yiruan-msft
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
Super User
Super User

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





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




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
OCT PBI Update Carousel

Power BI Monthly Update - October 2024

Check out the October 2024 Power BI update to learn about new features.

September Hackathon Carousel

Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

October NL Carousel

Fabric Community Update - October 2024

Find out what's new and trending in the Fabric Community.