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
Anonymous
Not applicable

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
jdbuchanan71
Super User
Super User

@Anonymous 

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

5 REPLIES 5
V-pazhen-msft
Community Support
Community Support

@Anonymous 

 

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.
 

 

jdbuchanan71
Super User
Super User

@Anonymous 

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.

 

@jdbuchanan71 I've been looking and working on a similar thing for 2 days now.

Your calculations are spot on! 

Thanks so much for sharing!

Anonymous
Not applicable

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

 

You have to look at the [Amount] calculculation related to the data in the [monthly cost] column.  For the total column of the 'Monthly Amount' table the date range is 1/1/2020 - 12/31/2023.  The sum of the [Amount] column that falls into that date range is correct.
To make an additive measure where the Total column shows the sum of the months you can use.

Additive = 
CALCULATE(
    SUMX(VALUES(Dates[Month Year]),[Amount])
)

jdbuchanan71_0-1612995347138.png

 

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.