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
matteddleman
Regular Visitor

monthly revenue based on sale, start and end date

I'm very new to Power BI and trying to learn DAX formulas. I have a table that includes Amount per month, Start Date and End Date. I also have a calendar table. I am trying to create a matrix that places the amount per month in each month that is between the start and end date. For example, below is a picture of a sample data table and the matrix result that I would like:

 

I've tried the following formula based on other posts I've found, but it only shows the monthly value in the start date.

= CALCULATE(sum(opportunities[Amount by Month (CON)]),Filter(opportunities,opportunities[CON - Start Date]<=CALCULATE(max('Calendar'[Date]))),FILTER(opportunities,opportunities[tac_constructionend]>=CALCULATE((min('Calendar'[Date])))))

 

Any help would be greatly appreciated. 

 

 

Table.PNG

 

1 ACCEPTED SOLUTION
v-yuezhe-msft
Employee
Employee

@matteddleman,

You can perform series of steps in Query Editor of Power BI Desktop and get expected result in Matrix visual.
1.JPG3.JPG

The steps in Query Editor generate the code in Advanced Editor, you can paste the code below to Advanced Editor of a blank query to test.
2.JPG

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WCijKz0pNLjFU0lEyNDAwAFIm+ob6RgaG5kCmmb6xAYQdqwNXagSUMIIoNUMotdA3NsRQagyUMIYoNUcotcRmqgnIZohSC4RSQwOEsbEA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Project = _t, #"Amount by Month (CON)" = _t, #"CON - Start Date" = _t, tac_constructionend = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Project", type text}, {"Amount by Month (CON)", Int64.Type}, {"CON - Start Date", type date}, {"tac_constructionend", type date}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each List.Dates([#"CON - Start Date"],Duration.Days(Duration.From([tac_constructionend]-[#"CON - Start Date"]))+1,#duration(1,0,0,0))),
    #"Expanded Custom" = Table.ExpandListColumn(#"Added Custom", "Custom"),
    #"Changed Type1" = Table.TransformColumnTypes(#"Expanded Custom",{{"Custom", type date}}),
    #"Calculated Start of Month" = Table.TransformColumns(#"Changed Type1",{{"Custom", Date.StartOfMonth, type date}}),
    #"Removed Duplicates" = Table.Distinct(#"Calculated Start of Month", {"Custom", "Project"})
in
    #"Removed Duplicates"


Regards,
Lydia

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

View solution in original post

3 REPLIES 3
v-yuezhe-msft
Employee
Employee

@matteddleman,

You can perform series of steps in Query Editor of Power BI Desktop and get expected result in Matrix visual.
1.JPG3.JPG

The steps in Query Editor generate the code in Advanced Editor, you can paste the code below to Advanced Editor of a blank query to test.
2.JPG

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WCijKz0pNLjFU0lEyNDAwAFIm+ob6RgaG5kCmmb6xAYQdqwNXagSUMIIoNUMotdA3NsRQagyUMIYoNUcotcRmqgnIZohSC4RSQwOEsbEA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Project = _t, #"Amount by Month (CON)" = _t, #"CON - Start Date" = _t, tac_constructionend = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Project", type text}, {"Amount by Month (CON)", Int64.Type}, {"CON - Start Date", type date}, {"tac_constructionend", type date}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each List.Dates([#"CON - Start Date"],Duration.Days(Duration.From([tac_constructionend]-[#"CON - Start Date"]))+1,#duration(1,0,0,0))),
    #"Expanded Custom" = Table.ExpandListColumn(#"Added Custom", "Custom"),
    #"Changed Type1" = Table.TransformColumnTypes(#"Expanded Custom",{{"Custom", type date}}),
    #"Calculated Start of Month" = Table.TransformColumns(#"Changed Type1",{{"Custom", Date.StartOfMonth, type date}}),
    #"Removed Duplicates" = Table.Distinct(#"Calculated Start of Month", {"Custom", "Project"})
in
    #"Removed Duplicates"


Regards,
Lydia

Community Support Team _ Lydia Zhang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
mede
Resolver I
Resolver I

Hi @matteddleman

Firstly, welcome to the community and congratulations on your first post.

 

Here is your solution:

As a first step, create a Calendar Table with DAX with "New Table" 

Dates = CALENDAR("2017/1/1","2017/12/31")

 

Let's assume your own data table is called Project.

For easiness i renamed your "opportunities..." as "Amount"

There will be no relationships between these Dates and Project tables, nevertheless your expected solution will be achieved by creating new measure with the following DAX formula:

 

Amount per month = Calculate(SUM(Project[Amount]), FILTER(Project,COUNTROWS(FILTER(VALUES(Dates[Date]),Project[Start Date] <= Dates[Date] && Project[End Date] >= Dates[Date] )) > 0))

OR

Amount per month = 
VAR per_month = Calculate(SUM(Project[Amount]), FILTER(Project,COUNTROWS(FILTER(VALUES(Dates[Date]),Project[Start Date] <= Dates[Date] && Project[End Date] >= Dates[Date] )) > 0))
RETURN if(per_month=0,0,per_month)

 

First option will populate only the months you have sales in (Apr-Oct), second option will populate all months from Jan-Dec where some months will have 0 as default value.

 

Let us know if you have any questions.

 

Worked great. Thank you so much for your help. 

The only issue I have now is that in the matrix I created, the subtotals are just a continuation of the monthly amount and do not actually subtotal. I read some other posts last night, and not sure if there is a solution, but you did such an amazing job on the other formula, I thought I would ask. 


Again, thanks for all the help. 

 

Matt

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.