Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
Hello Frends,
How to get average per day revenue based on selected date of the quarter considering the below situation.
While calculating average, all days need to be considered not only transaction days. Ageragex calculate based on transaction days.
a. Situation 1: Selected date is 31st May then number of days should be from 1st April to 31st May i.e.61 days to be divided from the given period revenue to get average per sales(including non-transaction days and Non-working days)
b. Situation 2: Selected date is 12st April then number of days should be from 1st April to 12st April i.e.12 days to be divided from the given period revenue to get average per sales (including non-transaction days and Non-working days)
c. Situation 3: selected date is 15th Dec then number of days should be from 1st October to 15th December ie. 75 days to be divided from the given period revenue to get average per sales (including non-transaction days and Non-working days)
Solved! Go to Solution.
Hi @prabhuk ,
You could calculate the days with the following DAX:
QTD Days =
VAR a =
EOMONTH ( MIN ( 'Table'[Sales Date] ), -1 )
VAR b =
MAX ( 'Table'[Sales Date] )
RETURN
DATEDIFF ( a, b, DAY )
Then calculate the total value and the result of each day. You could combine these measures as a single measure.
Total Sales =
SUM('Table'[Sale Value])
Per Day Sales =
DIVIDE([Total Sales],[QTD Days])
Here is the result.
Here is the test file for your reference.
@prabhuk , Try like
AverageX(Summarize(Table, Date[Date], "_1", Sum(Table[Value])),[_1])
Or
AverageX(Summarize(Date, Date[Date], "_1", Sum(Table[Value]), "_2",distinctcount(Date[Date])),divide([_1],[_2]))
This is avarage but what i requested is per days sales.
Below result i am getting from the formula.
Sales Date | Sale Value |
05-Jan-20 | 100 |
18-Jan-20 | 50 |
29-Jan-20 | 100 |
15-Feb-20 | 150 |
Average | 100 |
What is need is the below
Total Sales | 400 |
QTD Days( This case from 1st Jan to 15th Feb) | 46 |
Per Day Sales (400/46) | 8.70 |
Hi @prabhuk ,
You could calculate the days with the following DAX:
QTD Days =
VAR a =
EOMONTH ( MIN ( 'Table'[Sales Date] ), -1 )
VAR b =
MAX ( 'Table'[Sales Date] )
RETURN
DATEDIFF ( a, b, DAY )
Then calculate the total value and the result of each day. You could combine these measures as a single measure.
Total Sales =
SUM('Table'[Sale Value])
Per Day Sales =
DIVIDE([Total Sales],[QTD Days])
Here is the result.
Here is the test file for your reference.
Please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490
The most important parts are:
1. Sample data as text, use the table tool in the editing bar
2. Expected output from sample data
3. Explanation in words of how to get from 1. to 2.
User | Count |
---|---|
128 | |
109 | |
100 | |
65 | |
62 |
User | Count |
---|---|
137 | |
115 | |
102 | |
71 | |
61 |