cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Frequent Visitor

Advice on handling cumulative (monthly) calculations when you only have a start/end date

I need some advice on the best strategy on creating a report based on monthly costs, but my data source only has a Start and End date of the costs.  

 

Below is a sample of the dataset (note: the actual dataset has a lot more data for each item which will be used for filters/slicers, but below is the relevent data I need):

Itemmonthly coststart dateend date
11001/1/202012/1/2023
2506/1/202012/1/2020
3753/1/20208/1/2020
41001/1/20206/1/2021

 

But utlimately the type of output I need to create is this:

Item1/1/20202/1/20203/1/20204/1/20205/1/20206/1/20207/1/20208/1/20209/1/202010/1/202011/1/202012/1/2020
1100100100100100100100100100100100100
2     505050    
3  757575757575    
4

 

 

100

100100100100100100100100100100100
Monthly Total:200200275275275325325325200200200200
Cumulative:20040067595012251550187522002400260028003000
             

 

The monthly and Cumulative totals are calculated by that month's costs.

 

So my question is this:  What is the best strategy for extrapolating the monthly charges, costs, etc, when the original dataset only has a start/end month?  I do need to be able to display each individual month's charges (and later graph them).

 

I've had a couple of ideas, but they seem somewhat messy (such as trying to add columns to fill in the months between start/end, but that will lead to a very large table as this project is projected out several years), so I'm looking for better ideas.

 

Thanks.

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Super User IV
Super User IV

Re: Advice on handling cumulative (monthly) calculations when you only have a start/end date

@GnortenJones 

You will need a date table in your model but not connected to your data table.  Your visual uses the fields from the date table and filters based on the month-year being calculated.

Amount = 
VAR _Start = FIRSTDATE ( Dates[Date] )
VAR _End = LASTDATE ( Dates[Date] )
RETURN 
    CALCULATE(
        SUM ( 'Table'[monthly cost] ),
        'Table'[end date] >= _Start,
        'Table'[start date] <= _End
    )  

Once we have that we can do the comulative calc like so.

Cumulative = 
VAR _End = LASTDATE ( Dates[Date] )
RETURN
CALCULATE(
    SUMX(VALUES(Dates[Month Year]),[Amount]),
    FILTER(ALLSELECTED(Dates),
    Dates[Date] <= _End)
)

jdbuchanan71_0-1593408145714.png

I have attached my sample file for you to look at.

 

View solution in original post

3 REPLIES 3
Highlighted
Super User IV
Super User IV

Re: Advice on handling cumulative (monthly) calculations when you only have a start/end date

@GnortenJones 

You will need a date table in your model but not connected to your data table.  Your visual uses the fields from the date table and filters based on the month-year being calculated.

Amount = 
VAR _Start = FIRSTDATE ( Dates[Date] )
VAR _End = LASTDATE ( Dates[Date] )
RETURN 
    CALCULATE(
        SUM ( 'Table'[monthly cost] ),
        'Table'[end date] >= _Start,
        'Table'[start date] <= _End
    )  

Once we have that we can do the comulative calc like so.

Cumulative = 
VAR _End = LASTDATE ( Dates[Date] )
RETURN
CALCULATE(
    SUMX(VALUES(Dates[Month Year]),[Amount]),
    FILTER(ALLSELECTED(Dates),
    Dates[Date] <= _End)
)

jdbuchanan71_0-1593408145714.png

I have attached my sample file for you to look at.

 

View solution in original post

Highlighted
Microsoft
Microsoft

Re: Advice on handling cumulative (monthly) calculations when you only have a start/end date

@GnortenJones 

 

Try create a calendar and a calculate column for each item in the calendar table.

Calendar Table =   CALENDAR(DATE(2020,1,1),DATE(2020,12,31))

 

 

Item 1 = CALCULATE(MIN('Table'[monthly cost]),FILTER('Table',[Date]>='Table'[start date]&&'Calendar'[Date]<='Table'[end date]),'Table'[Item]=1)
Item 2 = CALCULATE(MIN('Table'[monthly cost]),FILTER('Table',[Date]>='Table'[start date]&&'Calendar'[Date]<='Table'[end date]),'Table'[Item]=2)
Item 3 = CALCULATE(MIN('Table'[monthly cost]),FILTER('Table',[Date]>='Table'[start date]&&'Calendar'[Date]<='Table'[end date]),'Table'[Item]=3)
Item 4 = CALCULATE(MIN('Table'[monthly cost]),FILTER('Table',[Date]>='Table'[start date]&&'Calendar'[Date]<='Table'[end date]),'Table'[Item]=4)

 

 

And use the date hierarchy to show year and month on the table visual.

monthly total.JPG

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

 

Highlighted
Frequent Visitor

Re: Advice on handling cumulative (monthly) calculations when you only have a start/end date

Thanks!  I think this gets me pretty close to where I want to be, though I am having trouble getting it to work perfectly with my actual data, but I'm sure that's likely user error on my part.

 

One thing though.  On your example (and on my actual data), the columns are showing the monthly costs correctly, but when you look at the totals for each row on the "Monthly Amount", they are showing the monthly costs, rather than the total cost.  The "Cumulative Amount" looks right.  (see screenshot).

 

Thanks for your help!

 

GnortenJones_0-1593698770525.png

 

Helpful resources

Announcements
Meet the 2020 Season 2 Power BI Super Users!

Meet the 2020 Season 2 Power BI Super Users!

Find out who's part of the program this season, and welcome the new Super Users.

August 2020 Community Challenge: Can You Solve These?

August 2020 Community Challenge: Can You Solve These?

We're excited to announce our first cross-community 'Can You Solve These?' challenge!

July 2020 Community Highlights

July 2020 Community Highlights

Learn about the exciting things that happened in July.

Featured Data Story of The Month

Featured Data Story of The Month

All Data Stories Gallery contributions are reviewed for each month. We select a contribution and feature the community member the following month.

Power BI Dev Camp - Developing with .NET Core

Power BI Dev Camp - Developing with .NET Core

Learn how to develop custom web applications for Power BI using .NET Core 3.1 and .NET 5.

Top Solution Authors
Top Kudoed Authors