Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
Hi Friends,
I need to calculate the average last 3-Month distinct store ordered our product.
Our product just released in March.
I tried the calculation using DATESINPERIOD Or DATEBETWEEN but it does not give me the expected result.
What I expect a result of average last 3-Month measure:
In June: ( 2259 + 1879 +1493 ) / 3 = 1877
In May: ( 1879 +1493) /2 = 1686 ( because only 2 months: April and Mar)
In Apr: 1493 ( Only Mar )
In Mar: Blank
Any help is highly appreciated.
Have a nice day, guys!
Solved! Go to Solution.
Hi @tracytran91,
Sorry for delay reply.
Try measure as:
Measure =
var curr_month=SELECTEDVALUE('Table'[Month])
var temp=
AVERAGEX(
FILTER(
ALLSELECTED('Table'[Month]),
'Table'[Month]<curr_month&&'Table'[Month]>=curr_month-3
),
DISTINCTCOUNT('Table'[Store Code]))
return temp
The pbix is attached.
If you still have some question, please don't hesitate to let me known.
Best Regards,
Link
Is that the answer you're looking for? If this post helps, then please consider Accept it as the solution. Really appreciate!
Hi @tracytran91
You can create a column as:
Month = MONTH('Table'[Invoice Date ])
Create a measure as:
Measure =
CALCULATE(
SUM('Table'[Sales])/3,
FILTER(
ALL('Table'),
'Table'[Month]>=MAX('Table'[Month])-3 && 'Table'[Month]<=MAX('Table'[Month])-1
)
)
Here is the output:
The pbix is attached.
Best Regards,
Link
If this post helps then please consider Accept it as the solution to help the other members find it more quickly.
Hi @v-xulin-mstf , Please kindly have a look desired result.
Sample data here:
https://drive.google.com/file/d/1mQzTZ53zI_3EJ4okIpNJCmC9G-IXOxDF/view?usp=sharing
I would like to know:
At Channel DOMESTIC with stores with sales bigger than 0 ( It means excluding return), What is the average last 3 Months of DISTINCT STORE ordered our product?
Hi @tracytran91,
Sorry for delay reply.
Try measure as:
Measure =
var curr_month=SELECTEDVALUE('Table'[Month])
var temp=
AVERAGEX(
FILTER(
ALLSELECTED('Table'[Month]),
'Table'[Month]<curr_month&&'Table'[Month]>=curr_month-3
),
DISTINCTCOUNT('Table'[Store Code]))
return temp
The pbix is attached.
If you still have some question, please don't hesitate to let me known.
Best Regards,
Link
Is that the answer you're looking for? If this post helps, then please consider Accept it as the solution. Really appreciate!
Hi,
Try these measures
Measure = averagex(summarize(filter(calendar,datesbetween(calendar[date],edate(min(calendar[date]),-3),min(calendar[date])-1)),Calendar[year],calendar(month name),"ABCD",[No. of distinct stores]),[abcd])
Hope this helps.
Hi @Ashish_Mathur , Could you please explain what ""ABCD" and [abcd] in your measure? So I can replace them with appropriate column/meaure.
Nothing. Just left ABCD stay there - it is just the column title of the second column of the virtual table.
There is the error for this measure. 😞
Hi,
Try this measure
=averagex(summarize(filter(values(calendar[month name]),datesbetween(calendar[date],edate(min(calendar[date]),-3),min(calendar[date])-1)),calendar(month name),"ABCD",[No. of distinct stores]),[abcd])
If this does not help, then share the link from where i can download your PBI file.
I tried once agin but it still does not work out. I created a sample excel file.
Here is the link : https://drive.google.com/file/d/1LFQWqvLV5cwDfWzcnjzNR5I6W4K1ZV7I/view?usp=sharing
I would like to know:
At Channel DOMESTIC with stores with sales bigger than 0 ( It means excluding return), What is the average last 3 Months of distinct store ordered our product?
Please kindly note that I have the Date table which has relationship with Sale table.
Your assistance regarding this matter is highly appreciated.
I created 2 measures test3 and Rolling3 to follow your idea. However, they gave me same result as I did before.
You can replace distinctcount of the customer with
averageX(values(D_date[Month]), calculate(distinctcount('F Secondary Sale[customer])))
or try like this example
Rolling 3 = divide( CALCULATE(sum(Sales[Sales]),DATESINPERIOD('Date'[Date ],MAX('Date'[Date]),-3,MONTH)) ,
CALCULATE(distinctCOUNT('Date'[Month Year]),DATESINPERIOD('Date'[Date],MAX('Date'[Date]),-3,MONTH), filter(Sales,not(isblank(sum(Sales[Sales]))))))
Hi,
Share your PBI file with the distinct store measure already written there.