Forum Discussion
Sum with Grouping
Are the groups static, or do they need to be dynamic based on a user selection?
If they're static, it's easy. Make a new field (preferably at your data source if you can, else in a query (e.g. in Power Query) before the data is added to the Power Pivot model) with those groups. Then you can just create visualizations against the new field that contains the group.
If they must be dynamic based on user selection, you'll have to give us some more detail on what your expected use case is, and the solution will be a bit more complex.
Hi Greg,
Since the function DateTime.Date(DateTime.LocalNow() - [Due Date]) will also return negative values--payment not due yet, I have to take out all the negative values. I change the condition to "if ((diff = 0) and (diff < 31))" but it did not work. What is the correct syntax for this "if and" expression?
Thanks
You can just add one more step to the if-then chain of statements in my originally suggested Power Query code:
let
diff = Number.From( DateTime.Date( DateTime.LocalNow() ) - [DueDate] )
,Bucket =
if diff < 0
then null
else if diff < 31
then "0-30"
else if diff < 61
then "31-60"
else if diff < 91
then "61-90"
else "Over 90"
in
Bucket'let' defines a local namespace, in which we can assign values to variables. The first we assign is the variable name 'diff' to the result of the calculation of the number of days between today and the due date.
We then refer to the value represented by 'diff' repeatedly in our definition of the next variable, 'Bucket'. Bucket stores a null or a text string based on the result of evaluating the if-then-else chain of statements, terminating on the first condition that returns true. I simply added one more check before all the others for a 'diff' value of < 0, in which case we return null.
to end the let statement, we use 'in', where we can define the output of the result of some calculation in that namespace. In our case we return the value of the variable 'Bucket'.
This entire statement is run once per row in the table, with the let statement being evaluated in the context of the values in that row.
Hi Greg,
Thank you for the answer and for being so thoughtly. I am going to digest the explanation now. :-)