one or four count measures required

frankee_gee

Board Regular
Joined
Mar 3, 2008
Messages
144
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
Hello Everyone,

Needed help with editing or creating measures/calculated fields. Find expected results @ the following link: https://dl.dropboxusercontent.com/u/68876442/AgingMeasureHelpb.xlsx


After researching a many forums I was able to create the initial measure with the result I was looking for:
--Return the count of each record for each month-end during the EDate and DDate period and if DDate is blank, use todays date as the "DDate"

Code:
AgingCount:=CALCULATE(CALCULATE(COUNTROWS(PReport),

            PReport[EDate] <= VALUES(t_CalendarFree[FullDate]),
  
            IF(ISBLANK(PReport[DDate]),today(), 
      
            PReport[DDate]) >= VALUES(t_CalendarFree[FullDate])), LASTDATE(t_CalendarFree[FullDate]))

BTW " t_CalendarFree " is not connected to any tables.

Now I have to organize the results into the following four buckets:
1.) Less than 90 days,
2.) 91-120 days,
3.) 121 - 180 days
4.) >181 days


Please help with suggestions and formula(s)

A.) Would I use 4 different measures and name each <90 days, 91-120 days...and so on? (If so, how would this be calculated)


Or

B.) Use the buckets ? similar to :https://blogs.msdn.microsoft.com/analysisservices/2014/06/05/bucketing-values-in-dax/ (I dont really have a clue how to set this up)


Or

C.) Use a single formula with the "Switch" function? (Again how would this look)


----------------------
I use the following in calculated columns. which only help with a daily snap shot.

DaysUp:
= IF([DDate]=BLANK(),[EDate]-TODAY(),[EDate]-[DDate])+1
(returns the number of days between EDate and DDate)


AgingDays
=IF([DDate]=0,BLANK(),SWITCH(TRUE(),
INT(PReport[DaysUp])<90,"Less than 90 days",
INT(PReport[DaysUp])<121,"90-120 days",
INT(PReport[DaysUp])<181,"121-180 days",
"> 180 days"))


It would be ideal to use ONE formula...because I will be showing aging with many different columns in pivot tables.

Thank you for your time in advance.

Frankee
 

Excel Facts

Best way to learn Power Query?
Read M is for (Data) Monkey book by Ken Puls and Miguel Escobar. It is the complete guide to Power Query.

Forum statistics

Threads
1,213,543
Messages
6,114,237
Members
448,555
Latest member
RobertJones1986

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top