Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi all,
I am trying to change a set analysis to read from a variable instead of a static value. The issue is the Dashboard contains only formulas related to dates. When the data source fails to update thus the data is from more than 1 week back, my Dashboard becomes blank.
FROM:
set EngCurrentWeek = Sum({<[# of Weeks from Current Week] ={'-1'}>}[EngagementsTW#])
SET EngPreviousWeek = Sum({<[# of Weeks from Current Week] ={'-2'}>}[EngagementsTW#])
TO:
//Find Max Date on QVD
Set vMaxWeek = week($(vMaxDate));
Set vCurrentWeek = Week(Today());
Set i = $(vCurrentWeek) - $(vMaxWeek);
set EngCurrentWeek = Sum({<[# of Weeks from Current Week] ={'-($(i))'}>}[EngagementsTW#])
SET EngPreviousWeek = Sum({<[# of Weeks from Current Week] ={'-($(i)-1)'}>}[EngagementsTW#])
The new formulas with the variables are not working. How can the set analysis use the variables? Is this a formatting issue?
Try using 'let' instead of 'set' to define your variables:
Let vMaxWeek = week($(vMaxDate));
Let vCurrentWeek = Week(Today());
Let i = $(vCurrentWeek) - $(vMaxWeek);
Try using 'let' instead of 'set' to define your variables:
Let vMaxWeek = week($(vMaxDate));
Let vCurrentWeek = Week(Today());
Let i = $(vCurrentWeek) - $(vMaxWeek);
Also try using variable in the set analysis like this:
Sum({<[# of Weeks from Current Week] ={'-($(=$(i)))'}>}[EngagementsTW#])
Hi,
"SET" works when we need constant values (we can say hard code) and "LET" is used to define variable values from a formula, LET can use funtions like today(), week(), Peek() ... just change SET by LET (as Gabriela said) and you can see funtions week() turns blue and variables $() turns gray.
Best regards.