Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
8 years ago
Solved

Create Aging Bucket. - formula help.

Hi Power BI gurus,    I am farily new to Power BI,  might ask some simple questions, but please kindly help!   :)   I am trying to create couple new fields related to Aging bucket ( Account recei...
Formula
  • v-qiuyu-msft's avatar
    v-qiuyu-msft
    8 years ago

    Hi Anonymous,

     

    You can create a custom column in Query Editor use Power Query below: 

     

    =if [date_diff] <=1 then "current" else if [date_diff] > 1 and [date_diff] <30 then "1-30 Days" else if [date_diff]>= 30 and [date_diff] < 60 then "31-60 Days" else null

     

     

    To create a measure, we need to back to report level use DAX. 

     

    Measure = IF(MAX([date_diff]) <=1 , "current",IF( MAX([date_diff]) > 1 && MAX([date_diff])<30,  "1-30 Days",IF(MAX([date_diff])>= 30 && MAX([date_diff]) < 60, "31-60 Days",  BLANK())))

     

     

    You can downlaod attached pbix file to have a look. 

     

    Best Regards,
    Qiuyun Yu 

v-qiuyu-msft's avatar
v-qiuyu-msft
Community Support
8 years ago

Hi Anonymous,

 

You can create a custom column in Query Editor use Power Query below: 

 

=if [date_diff] <=1 then "current" else if [date_diff] > 1 and [date_diff] <30 then "1-30 Days" else if [date_diff]>= 30 and [date_diff] < 60 then "31-60 Days" else null

 

 

To create a measure, we need to back to report level use DAX. 

 

Measure = IF(MAX([date_diff]) <=1 , "current",IF( MAX([date_diff]) > 1 && MAX([date_diff])<30,  "1-30 Days",IF(MAX([date_diff])>= 30 && MAX([date_diff]) < 60, "31-60 Days",  BLANK())))

 

 

You can downlaod attached pbix file to have a look. 

 

Best Regards,
Qiuyun Yu 

Anonymous's avatar
Anonymous
Not applicable
8 years ago

HI Qiuyun,

 

This is exactly what I am looking for, and thanks for sharing more than one solutions.  Good to learn both.   :)

 

Thank you!!

 

Flora