Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
prabhuk
Helper I
Helper I

Average per day revenue of the quarter

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)

 

1 ACCEPTED 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.

1-1.PNG

Here is the test file for your reference.

 

Community Support Team _ Eads
If this post helps, then please consider Accept it as the solution to help the other members find it.

View solution in original post

4 REPLIES 4
amitchandak
Super User
Super User

@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 DateSale Value
05-Jan-20100
18-Jan-2050
29-Jan-20100
15-Feb-20150
Average100

 

What is need is the below

Total Sales400
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.

1-1.PNG

Here is the test file for your reference.

 

Community Support Team _ Eads
If this post helps, then please consider Accept it as the solution to help the other members find it.
Greg_Deckler
Super User
Super User

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.


@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.