Forum Discussion
Inputs by customer tracking
I have a database like this to record when a customer sends us an item.
DOC_NO ACCTNO ENTER_DATE QTY
123 15423 2017-04-17 1
124 10184 2017-04-17 1
.....
I have date dimensions table that I've linked the enter_date to.
What is the easiest way to track inputs variances, by a customer, for 30 days, 60 days and 90-day periods?
I need information like this: Customer 15423 has sent me 12 less units over the last 30 days compared to the 30 days prior. I would like to have a slicer were I can select 30 day, 60 day, 90 day.
Hi Anonymous,
You can refer to below steps to calculate with selected day range.
1. Create a table to store the day range.
Selector Range = UNION(ROW("Range",30),ROW("Range",60),ROW("Range",90))2. Add a measure to get the chose item from "Selector Range" table.
3. Create a slicer with "range" column of "Selector Range" table.
4. Add a measure to calculate on chosen item.
Calculate Range = var currDate=MAX(test[ENTER_DATE]) return SUMX(FILTER(ALL(test),[ACCTNO]=MAX([ACCTNO])&&[ENTER_DATE]>=DATE(YEAR(currDate),MONTH(currDate),DAY(currDate)-[Selected Item])&&[ENTER_DATE]<=currDate),[QTY])
5. Drag a table visual to store the result.
Regards,
Xiaoxin Sheng
1 Reply
Hi Anonymous,
You can refer to below steps to calculate with selected day range.
1. Create a table to store the day range.
Selector Range = UNION(ROW("Range",30),ROW("Range",60),ROW("Range",90))2. Add a measure to get the chose item from "Selector Range" table.
3. Create a slicer with "range" column of "Selector Range" table.
4. Add a measure to calculate on chosen item.
Calculate Range = var currDate=MAX(test[ENTER_DATE]) return SUMX(FILTER(ALL(test),[ACCTNO]=MAX([ACCTNO])&&[ENTER_DATE]>=DATE(YEAR(currDate),MONTH(currDate),DAY(currDate)-[Selected Item])&&[ENTER_DATE]<=currDate),[QTY])
5. Drag a table visual to store the result.
Regards,
Xiaoxin Sheng