Forum Discussion

VinhDam's avatar
VinhDam
New Member
10 years ago
Solved

Sum with Grouping

Hi everyone,   I have a simple table like below:   Sales  Aging $100   1 $200   2 $300   5 $400   9 $500   15   I would like to to perform different sums based on aging, for example, sum o...
  • greggyb's avatar
    greggyb
    10 years ago

    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.

VinhDam's avatar
VinhDam
New Member
10 years ago

Hi everyone again,

 

I am being tasked with producing some financial reports using Power BI.  One of the reports is Receivable Aging.  The two columns to be used in the Receivable Aging report are Amount Due and Due Date.  The Aging periods are 0-30 Days, 31-60 Day, 61-90 Days, Over 90 Days.

 

I was able to extract the aging days, DurationDays, for each row, but I don't know how to create new tables for each of the aging periods.

 

My previous example has been simplified.  The example below is much more realistic:

 

Amount Due     Due Date   

$100                 1/16/2016 

$200                 2/16/2016

$300                 5/14/2015

$400                 7/25/2014

$500                 1/9/2016

$600                 5/12/2015

$700                 6/4/2016

 

Expected Report:

 

Aging       Amount

0-30         $500

31-60       $0

61-90       $0

Over 90    $1,300

 

Also, would you recommend a book on DAX, Power Query, with an emphasis on programming?  

 

Many thanks for your help again.

 

 

greggyb's avatar
greggyb
Resident Rockstar
10 years ago

Create a new field using Power Query when you're importing data.

Add a custom column using the following Power Query code:

let
  diff = Number.From( DateTime.Date( DateTime.LocalNow() ) - [DueDate] )
  ,Bucket =
    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

This assigns the difference in days between the date of model processing (assuming you process daily, this means today's date) and the due date to the local variable, diff.

 

Bucket is then assigned a text value based on the value of diff. This gives you labels which can be used for any measures in your model.

 

I'd recommend assigning an integer key to each of the buckets and having the labels in a separate dimension, but that's up to you. You can use the same logic for both key and label.