Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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.
Solved! Go to Solution.
@matteddleman,
You can perform series of steps in Query Editor of Power BI Desktop and get expected result in Matrix visual.
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.
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
@matteddleman,
You can perform series of steps in Query Editor of Power BI Desktop and get expected result in Matrix visual.
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.
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
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
User | Count |
---|---|
128 | |
108 | |
99 | |
65 | |
62 |
User | Count |
---|---|
137 | |
115 | |
102 | |
71 | |
61 |