Forum Discussion
90 Day Usage Formula
Hi!
I am creating a customer report for items purchased. We need to include a 90 day and 180 day usage measure in this report. What would the measure be for this?
90 Day Usage = calculate(Sum(qty_shipped), datesinperiod(
but not sure how I would fill in the datesinperiod.
Any help is appreciated!!
Hello! This solution assumes you have a date dimension table marked as a date table.
First, I would create a measure for Total Quantity Shipped (avoid abbreviations in your naming of measures and columns so that it will work well with Copilot if and when you use it).
Total Quantity Shipped = SUM('YourTable'[qty_shipped])
Then, create below:
Total Quantity Shipped (Last 90 Days) =
VAR LastDate = LASTDATE('DateTable'[Date])
VAR Date90DaysAgo = LastDate-90
RETURN
CALCULATE ([Total Quantity Shipped], DATESBETWEEN('DateTable'[Date],Date90DaysAgo, LastDate))
Hmmmm - try changing the LastDate VAR to say MaxDate instead, then change it everywhere else in the measure. I forgot, LastDate is reserved.
Total Quantity Shipped (Last 90 Days) =
VAR MaxDate = LASTDATE('DateTable'[Date])
VAR Date90DaysAgo = MaxDate-90
RETURN
CALCULATE ([Total Quantity Shipped], DATESBETWEEN('DateTable'[Date],Date90DaysAgo, MaxDate))
7 Replies
Hello! This solution assumes you have a date dimension table marked as a date table.
First, I would create a measure for Total Quantity Shipped (avoid abbreviations in your naming of measures and columns so that it will work well with Copilot if and when you use it).
Total Quantity Shipped = SUM('YourTable'[qty_shipped])
Then, create below:
Total Quantity Shipped (Last 90 Days) =
VAR LastDate = LASTDATE('DateTable'[Date])
VAR Date90DaysAgo = LastDate-90
RETURN
CALCULATE ([Total Quantity Shipped], DATESBETWEEN('DateTable'[Date],Date90DaysAgo, LastDate))
Hmmmm - try changing the LastDate VAR to say MaxDate instead, then change it everywhere else in the measure. I forgot, LastDate is reserved.
Total Quantity Shipped (Last 90 Days) =
VAR MaxDate = LASTDATE('DateTable'[Date])
VAR Date90DaysAgo = MaxDate-90
RETURN
CALCULATE ([Total Quantity Shipped], DATESBETWEEN('DateTable'[Date],Date90DaysAgo, MaxDate))