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
PBI_User Group Leader_768x460.jpg

Manage your user group events

Check out the News & Announcements to learn more.

MBAS on Demand

2021 Release Wave 2 Plan

Power Platform release plan for the 2021 release wave 2 describes all new features releasing from October 2021 through March 2022.

Get Ready for Power BI Dev Camp

Microsoft named a Leader in The Forrester Wave

Microsoft received the highest score of any vendor in both the strategy and current offering categories.

R2 (Green) 768 x 460px.png

Microsoft Dynamics 365 & Power Platform User Professionals

DynamicsCon is a FREE, 4 half-day virtual learning experience for 11,000+ Microsoft Business Application users and professionals.