Repeat sum over date range

[AdamM]

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?

[fhill · Accepted solution]

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
RemovedColumn

2.  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

[v-yulgu-msft]

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

[Ashish_Mathur · Accepted solution]

Hi @AdamM,

You may refer to my solution here.

Hope this helps.

[AdamM]

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

[Ashish_Mathur]

You are welcome.

[fhill · Accepted solution]

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
RemovedColumn

2.  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