topic Re: Need Help to calculate the # of days since a customer's last invoice, and later get an avg in DAX Commands and Tips

Need Help to calculate the # of days since a customer's last invoice, and later get an avg

OCBB_SFAFPandA — Thu, 26 May 2022 23:59:31 GMT

Hello,

I was able to create a measure for the last date serviced for each customer. However, when I try to create an AVERAGEX measure, I am not able to bring in the data i need (I think it's because I created a measure instead of a calculated column).

My goal is to get the last invoice date for each customer, then determin how many days have gone by for each customer, then get an aggregate avg # days not invoiced to summarize.

Would you have any advice?

Re: Need Help to calculate the # of days since a customer's last invoice, and later get an avg

Whitewater100 — Fri, 27 May 2022 00:31:24 GMT

Hi:

If you have a Date Table connected to your Fact Table(Sales Table) -then these measures should work.

* Table is usually the sales table. I’m using Table below..

Date of Last Purchase = LASTDATE(Table[Purchase Date])

Last Purchase Date = MAXX(ALL(Table), Table[Purchase Date])

Days Since Last Purchase = IF(ISBLANK( [Date of Last Purchase]), BLANK(), VALUE([Last Purchase Date] – [Date of Last Purchase]))

Avg Days = AVERAGEX(Table, [Days Since Last Purchase])

I hope you find this as a solution. Good luck. Thanks...

Re: Need Help to calculate the # of days since a customer's last invoice, and later get an avg

Whitewater100 — Fri, 27 May 2022 00:33:34 GMT

Hi Again:

If you need a DAX Date Table, I will paste code below. You'll want to go to MODELING> NEW TABLE

after you do this, then mark as a date table(date field) and create a realtionship with your sales/fact table on date to purchase date.

Dates =

-- Specify a start date and end date

VAR StartDate = Date(2021,1,1)

VAR EndDate = Today() + 243

VAR FiscalMonthEnd = 12

-- Generate a base table of dates

VAR BaseTable = Calendar(StartDate, EndDate)

-- Add the Year for each individual date

VAR Years = ADDCOLUMNS(BaseTable,"Year",YEAR([Date]))

-- Add the calendar month and other month related data for each date

VAR Months = ADDCOLUMNS(

Years,

"Month",MONTH([Date]),

"Year and Month Number",FORMAT([Date],"YYYY-MM"),

"Year and Month Name",FORMAT([Date],"YYYY-MMM"),

"Fiscal Year", IF( FiscalMonthEnd = 12, YEAR([Date]), IF( MONTH([DATE]) <= FiscalMonthEnd, YEAR([DATE])-1, YEAR([Date]))),

"Fiscal Month", IF( FiscalMonthEnd = 12, MONTH([Date]),

IF( MONTH([Date]) <= FiscalMonthEnd, FiscalMonthEnd + MONTH([Date]), MONTH([Date]) - FiscalMonthEnd))

)

-- Add the Quarter and other quarter related data for each date

VAR Quarters = ADDCOLUMNS(

Months,

"Quarter",ROUNDUP(MONTH([Date])/3,0),

"Year and Quarter",[Year] & "-Q" & ROUNDUP(MONTH([Date])/3,0))

-- Add the Day and other day related data for each date

VAR Days = ADDCOLUMNS(

Quarters,

"Day",DAY([Date]),

"Day Name",FORMAT([Date],"DDDD"),

"Day Of Week",WEEKDAY([Date]),

"Day Of Year", DATEDIFF (DATE(YEAR([Date]),1,1), [Date], DAY) + 1)

-- Add the Week (assuming each week starts on a Sunday) and other week related data for each date

VAR Weeks = ADDCOLUMNS(

Days,

"Week Of Month (Sunday)",INT((DAY([Date])-1)/7)+1,

"Week of Year (Sunday)",WEEKNUM([Date],1),

"Year and Week (Sunday)",[Year] & "-W" & WEEKNUM([Date],1))

-- Add an 'Is Working Day' column which will be true for all days but Saturday and Sunday.

var WorkingDays = ADDCOLUMNS(

Weeks,

"Is Working Day", NOT WEEKDAY( [Date] ) IN {1,7})

RETURN WorkingDays

Re: Need Help to calculate the # of days since a customer's last invoice, and later get an avg

OCBB_SFAFPandA — Fri, 27 May 2022 02:18:53 GMT

Thanks!