cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
ganesanr30
Regular Visitor

Please Help us to Solve the measure

Hi,

I am trying to get per day revenue based on the given start_date and end_date .Can anybody help to try to get in measure .

 

Revenue Per day.PNG

 

Eg : I want to view the invoice amount for the any intermediate date (Say From Date 01-06-2021 to 30-06-2021 Result will be 32.60 for Invoice number 1) 

Kindly Help us to solve in measure 

thanks in advance  

1 ACCEPTED SOLUTION
Jihwan_Kim
Community Champion
Community Champion

Hi, @ganesanr30 

I am not sure how your desired outcome looks like, but please check the below picture and the sample pbix file's link down below.

All measures are in the sample pbix file.

 

I suggest having a dim-date table like below.

 

Picture1.png

 

Amount Total Step 1 =
CALCULATE (
SUMX (
VALUES ( Dates[Date] ),
SUMX (
Invoices,
Invoices[Amt]
/ ( DATEDIFF ( Invoices[Start_Date], Invoices[End_Date], DAY ) + 1 )
)
),
FILTER (
Invoices,
Invoices[Start_Date] <= MAX ( Dates[Date] )
&& Invoices[End_Date] >= MIN ( Dates[Date] )
)
)
 
 
Amount Total step 2 =
SUMX ( VALUES ( Dates[Date] ), [Amount Total Step 1] )
 
 
 
 

Hi, My name is Jihwan Kim.


If this post helps, then please consider accept it as the solution to help other members find it faster, and give a big thumbs up.


Linkedin: linkedin.com/in/jihwankim1975/

Twitter: twitter.com/Jihwan_JHKIM


If this post helps, then please consider accept it as the solution to help other members find it faster, and give a big thumbs up.

Best regards, JiHwan Kim

Linkedin: https://www.linkedin.com/in/jihwankim1975/

Twitter: https://twitter.com/Jihwan_JHKIM

View solution in original post

2 REPLIES 2
Jihwan_Kim
Community Champion
Community Champion

Hi, @ganesanr30 

I am not sure how your desired outcome looks like, but please check the below picture and the sample pbix file's link down below.

All measures are in the sample pbix file.

 

I suggest having a dim-date table like below.

 

Picture1.png

 

Amount Total Step 1 =
CALCULATE (
SUMX (
VALUES ( Dates[Date] ),
SUMX (
Invoices,
Invoices[Amt]
/ ( DATEDIFF ( Invoices[Start_Date], Invoices[End_Date], DAY ) + 1 )
)
),
FILTER (
Invoices,
Invoices[Start_Date] <= MAX ( Dates[Date] )
&& Invoices[End_Date] >= MIN ( Dates[Date] )
)
)
 
 
Amount Total step 2 =
SUMX ( VALUES ( Dates[Date] ), [Amount Total Step 1] )
 
 
 
 

Hi, My name is Jihwan Kim.


If this post helps, then please consider accept it as the solution to help other members find it faster, and give a big thumbs up.


Linkedin: linkedin.com/in/jihwankim1975/

Twitter: twitter.com/Jihwan_JHKIM


If this post helps, then please consider accept it as the solution to help other members find it faster, and give a big thumbs up.

Best regards, JiHwan Kim

Linkedin: https://www.linkedin.com/in/jihwankim1975/

Twitter: https://twitter.com/Jihwan_JHKIM

View solution in original post

Fowmy
Super User IV
Super User IV

@ganesanr30 

Hope you have a dates table, if not create one and connect to your data table and add the following measure:

amount =
VAR __start =
    MIN ( dates[date] )
VAR __end =
    MAX ( dates[date] )
RETURN
    SUMX (
        FILTER ( table, table[startdate] >= __start && table[enddate] <= __end ),
        table[amt]
    )
Did I answer your question? Mark my post as a solution! and hit thumbs up


Subscribe and learn Power BI from these videos

Website LinkedIn PBI User Group

Helpful resources

Announcements
PBI_User Group Leader_768x460.jpg

Manage your user group events

Check out the News & Announcements to learn more.

MBAS on Demand

2021 Release Wave 2 Plan

Power Platform release plan for the 2021 release wave 2 describes all new features releasing from October 2021 through March 2022.

Get Ready for Power BI Dev Camp

Microsoft named a Leader in The Forrester Wave

Microsoft received the highest score of any vendor in both the strategy and current offering categories.

R2 (Green) 768 x 460px.png

Microsoft Dynamics 365 & Power Platform User Professionals

DynamicsCon is a FREE, 4 half-day virtual learning experience for 11,000+ Microsoft Business Application users and professionals.