- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Printer Friendly Page
calculate "around" a variable
- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content
12-19-2018 09:48 AM
I am attempting to use variables within a measure, but not getting the desired results. I've looked around at all kinds of DAX reference material and can't figure out why this doesn't work. I know there are other ways to accomplish why I'm trying to do, but I'd like to know specifically why this doesn't work.
Basically, I'm setting a variable and then trying to recalculate that variable within the same measure. The recalculation doesn't work.
Data (table name is PD):
Dt | Value |
1/1/2018 | 100 |
1/2/2018 | 200 |
1/3/2018 | 250 |
1/4/2018 | 300 |
1/5/2018 | 400 |
Dt has a relationship to a Date table (DateTab)
Creating a measure that finds the difference in Value from the previous day:
Delta = var __TodayVal = SUM(PD[Value]) var __PrevVal = CALCULATE(__TodayVal, PREVIOUSDAY(DateTab[Date])) var __Delta = __TodayVal -__PrevVal return __Delta
Here is what I would expect
Dt | Value | Delta |
1/1/2018 | 100 | |
1/2/2018 | 200 | 100 |
1/3/2018 | 250 | 50 |
1/4/2018 | 300 | 50 |
1/5/2018 | 400 | 100 |
But this is what I get
I'm sure it has something to do with evaluation context inside the measure, but I'm wracking my brain to be able to explain it. I know I can work around it by creating multiple measures, but I'd like to know a concrete reason for why this can't be done in a single measure.
Thanks,
David
Solved! Go to Solution.
Accepted Solutions
Re: calculate "around" a variable
- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content
12-19-2018 11:11 AM
A variable in DAX is actually a constant. Unlike variables in other programming languages, once you declare a variable in DAX by assigning it a value, that value is immutable. Your variable __TodayVal in the CALCULATE for __PrevVal will not be affected at all by the filter argument.
All Replies
Re: calculate "around" a variable
- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content
12-19-2018 11:11 AM
A variable in DAX is actually a constant. Unlike variables in other programming languages, once you declare a variable in DAX by assigning it a value, that value is immutable. Your variable __TodayVal in the CALCULATE for __PrevVal will not be affected at all by the filter argument.
Re: calculate "around" a variable
- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content
12-19-2018 11:17 AM
Re: calculate "around" a variable
- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content
12-19-2018 11:23 AM
Hi @AlB - so what you are saying is that once __TodayVal is evaluated it is the value itself, not the code that populated it. And applying a different filter context to a constant does nothing.
Thanks!
David
Re: calculate "around" a variable
- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content
12-19-2018 03:31 PM
Hi @dedelman_clng,
In order to make this work you need to have your variable to be based on the context so it will move accordingly to your value in each line for this you should use redo your measure to this:
Delta = VAR first_date = CALCULATE ( MIN ( Table1[Dt] ); ALL ( Table1[Dt] ) ) VAR date_selection = CALCULATE ( MAX ( Table1[Dt] ); FILTER ( ALL ( Table1[Dt] ); Table1[Dt] < MAX ( Table1[Dt] ) ) ) RETURN IF ( MIN ( Table1[Dt] ) = first_date; BLANK (); SUM ( Table1[Value] ) - CALCULATE ( SUM ( Table1[Value] ); FILTER ( ALL ( Table1[Dt] ); Table1[Dt] = date_selection ) ) )
Explanation
First_Date = Used only to return blank on the first row, if place only the last part of the measure on the first row value of Delta will be the same as on the column value
Date_selection = This is a variable that calculates the maximum date for all dates below the row context so for january the second this measure give january first, and so on
After the return what I'm calculating is the sum for the current date (row context) and then picking up the Date Selection variable and get that specific day.
Having the measure like this will give you a variable based on row context and not a constant value, since I'm changing the context of the variable within itself.
The measure is also created in this way allows you to have leaps on your dates in order to always get the latest number and not have some lines with 0 or blanks and others with the full value, As you can see I go from january 5th to February 20 and the delta is calculated so if you have weekends or not continuous dates it will always return delta value for previous row.
@dedelman_clng and @AlB sorry for entering in this discussion but the context of a measure can be change at different levels not only in the visuals, and the variables within the measures can themselve have a change in context based on the way you create them so the variables are not constants depends on the way you build them.
Regards,
MFelix
Did I answer your question? Mark my post as a solution!
Proud to be a Datanaut!
Re: calculate "around" a variable
[ Edited ]- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content
12-20-2018 02:15 AM - edited 12-20-2018 05:01 AM
@dedelman_clng wrote:Hi @AlB - so what you are saying is that once __TodayVal is evaluated it is the value itself, not the code that populated it. And applying a different filter context to a constant does nothing.
Thanks!
David
Yep. Exactly
You would need a measure instead for what you were trying to do.
Re: calculate "around" a variable
- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content
12-20-2018 02:22 AM
@MFelix
Good point. I guess it comes down to nomenclature, to what exactly your definition of a constant entails.
What I said, or meant to say, is that once the variable is assigned a value (at creation) its contents are immutable. I am referring here to each execution instance of a variable. An instance of that variable exists between the point where it is created/assigned a value (which happens simultaneously) and the point where it is "used up" through RETURN.
When in a calculated column we have something like:
VAR CurrentID = 'Table'[ID] RETURN CALCULATE ( SUM ( 'Table'[Sales] ); FILTER ( 'Table'; 'Table'[ID] < CurrentID ) )
we'll have, for each row, a different instance of the variable CurrentID. I am limiting my definition of constant to what happens within a row, which is the effective existence of each instance of CurrentID. And I emphasized that aspect because it was the culprit
in the issue presented by @dedelman_clng
You are extending the definition to encompass all execution instances. If you consider it so, it is true that each instance will (possibly) be created with a different value. But that value will be immutable during a particular instance's life.
Re: calculate "around" a variable
[ Edited ]- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content
12-20-2018 04:25 AM - edited 12-20-2018 04:27 AM
Hi @AlB,
I agree with you and what you say, but one of the good things in DAX is that we can make changes of context within the measures themselves and not only considering the row context but also the table context, or any other, and you can create your own context that is different from the model or the visual you are using.
This make it's very dynamic and more flexible. But good remarks.
Regards,
MFelix
Did I answer your question? Mark my post as a solution!
Proud to be a Datanaut!
Re: calculate "around" a variable
- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content
12-20-2018 05:08 AM
Cool. Thanks for making that point, as using the term constant might indeed lead to misinterpretation.
Cheers
Re: calculate "around" a variable
- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content
12-20-2018 06:03 AM
Thanks @MFelix - this makes sense.
Looking at your code and explanation, it still looks like you're not "recalculating" the variable in a different context within the same call, which is what I was trying to accomplish. The solution you provide is very elegant and definitely much more flexible than assuming a continuum of days.
David