Forum Discussion
Repeat sum over date range
I'm struggling with how to use DAX to repeatedly SUM a value for each month if it falls within a date range.
This is a sample of my data:
| Monthly Savings | Savings Effective Date | Savings End Date |
| 10,000 | 1/1/2017 | 6/30/2017 |
| 15,000 | 1/1/2017 | 12/31/2017 |
This is the output that I want:
| Month | Monthly Savings |
| Jan | 25,000 |
| Feb | 25,000 |
| Mar | 25,000 |
| Apr | 25,000 |
| May | 25,000 |
| Jun | 25,000 |
| Jul | 15,000 |
| Aug | 15,000 |
| Sep | 15,000 |
| Oct | 15,000 |
| Nov | 15,000 |
| Dec | 15,000 |
What is the most efficient way to do this?
1. You need to create a Table with just MONTHS in it to calculate your values per month. You can do this in Excel and Copy/Paste into PowerBI, or here's code the 'Advanced Editor' code form Query Editor to build a list -100 months back to +100 months foward. (You can edit the 2nd row as needed.)
let
Source = Table.FromList({-100..100}, each{_}),
AddedStartOfMonth = Table.AddColumn(Source, "StartOfMonth", each Date.StartOfMonth(Date.AddMonths(Date.From(DateTime.LocalNow()),[Column1])), type date),
AddedEndOfMonth = Table.AddColumn(AddedStartOfMonth, "EndOfMonth", each Date.EndOfMonth([StartOfMonth]), type date),
RemovedColumn = Table.RemoveColumns(AddedEndOfMonth,{"Column1"})
in
RemovedColumn2. Here is a sample of the Month data & a sample of your table formatting:
3. Here's the DAX Column code for a column created on the *** Month Table *** that Sums Monthly Savings for each month if the Month Start date falls between your Eff Date & End Date.
Monthly Savings = CALCULATE(SUM(Table1[Monthly Savings]), FILTER(ALL(Table1), Query1[StartOfMonth] >= Table1[Eff Date] && Query1[StartOfMonth] <= Table1[End Date]))
*** BTW, this assums solid months Start & end Dates. If this could start/ stop mid-month, you need to create a DAILY table of every day, Create a 'Daily Savings' value in your raw data (DateDiff your two dates + 1 / Monthly Savings, use simular code to find a Daily Savings value, and then SUM up the Days into Months using PowerBI Tables or another DAX Measure.
FOrrest
5 Replies
Ashish,
Thank you! Your solution produced the daily savings amounts which enabled the ability to aggregate in different ways when I join my savings data with other related datasets. I believe expanding to a record for savings dollars each day is the ideal approach since it will be the most flexible as my reporting requirements change over time.
Adam
You are welcome.
1. You need to create a Table with just MONTHS in it to calculate your values per month. You can do this in Excel and Copy/Paste into PowerBI, or here's code the 'Advanced Editor' code form Query Editor to build a list -100 months back to +100 months foward. (You can edit the 2nd row as needed.)
let
Source = Table.FromList({-100..100}, each{_}),
AddedStartOfMonth = Table.AddColumn(Source, "StartOfMonth", each Date.StartOfMonth(Date.AddMonths(Date.From(DateTime.LocalNow()),[Column1])), type date),
AddedEndOfMonth = Table.AddColumn(AddedStartOfMonth, "EndOfMonth", each Date.EndOfMonth([StartOfMonth]), type date),
RemovedColumn = Table.RemoveColumns(AddedEndOfMonth,{"Column1"})
in
RemovedColumn2. Here is a sample of the Month data & a sample of your table formatting:
3. Here's the DAX Column code for a column created on the *** Month Table *** that Sums Monthly Savings for each month if the Month Start date falls between your Eff Date & End Date.
Monthly Savings = CALCULATE(SUM(Table1[Monthly Savings]), FILTER(ALL(Table1), Query1[StartOfMonth] >= Table1[Eff Date] && Query1[StartOfMonth] <= Table1[End Date]))
*** BTW, this assums solid months Start & end Dates. If this could start/ stop mid-month, you need to create a DAILY table of every day, Create a 'Daily Savings' value in your raw data (DateDiff your two dates + 1 / Monthly Savings, use simular code to find a Daily Savings value, and then SUM up the Days into Months using PowerBI Tables or another DAX Measure.
FOrrest
Hi AdamM,
Create a calendar table.
Calendar = FILTER ( CALENDAR ( MIN ( Savings[Savings Effective Date] ), MAX ( Savings[Savings End Date] ) ), DAY ( [Date] ) = 1 )Create a calculated table via CrossJoin.
CrossJoin = ADDCOLUMNS ( FILTER ( CROSSJOIN ( Savings, 'Calendar' ), 'Calendar'[Date].[MonthNo] >= Savings[Savings Effective Date].[MonthNo] && 'Calendar'[Date].[MonthNo] <= Savings[Savings End Date].[MonthNo] ), "MonthName", 'Calendar'[Date].[Month] )Add corresponding fields into table visual.
Best regards,
Yuliana Gu
