Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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):
Item | monthly cost | start date | end date |
1 | 100 | 1/1/2020 | 12/1/2023 |
2 | 50 | 6/1/2020 | 12/1/2020 |
3 | 75 | 3/1/2020 | 8/1/2020 |
4 | 100 | 1/1/2020 | 6/1/2021 |
But utlimately the type of output I need to create is this:
Item | 1/1/2020 | 2/1/2020 | 3/1/2020 | 4/1/2020 | 5/1/2020 | 6/1/2020 | 7/1/2020 | 8/1/2020 | 9/1/2020 | 10/1/2020 | 11/1/2020 | 12/1/2020 |
1 | 100 | 100 | 100 | 100 | 100 | 100 | 100 | 100 | 100 | 100 | 100 | 100 |
2 | 50 | 50 | 50 | |||||||||
3 | 75 | 75 | 75 | 75 | 75 | 75 | ||||||
4 |
100 | 100 | 100 | 100 | 100 | 100 | 100 | 100 | 100 | 100 | 100 | 100 |
Monthly Total: | 200 | 200 | 275 | 275 | 275 | 325 | 325 | 325 | 200 | 200 | 200 | 200 |
Cumulative: | 200 | 400 | 675 | 950 | 1225 | 1550 | 1875 | 2200 | 2400 | 2600 | 2800 | 3000 |
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.
Solved! Go to Solution.
@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)
)
I have attached my sample file for you to look at.
@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.
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.
@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)
)
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!
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!
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])
)
User | Count |
---|---|
124 | |
108 | |
99 | |
62 | |
62 |
User | Count |
---|---|
137 | |
115 | |
102 | |
71 | |
61 |