Seeking assistant and suggestion to create a monthly chart between two dates

[eryka_90]

Hi,

I'm stucked when to create a chart monthly saving and seeking expert guide or suggestion to proceed further. My sample data as below:

(*) is the measure in my Table A

ID	ExecutionStartDatefortheProject	EstimatedValidatedDate	*FirstYearDefinition	*SecondYearDefinition	*YearDefinition	Actual Saving ($)	*MonthsInYear
123	2022-05-18	2023-12-31	2022	2023	ActualFirstYear	20,700	8
123	2022-05-18	2023-12-31	2022	2023	ActualSecondYear	10,500	12

I added new Date Table B refer to Min (ExecutionStartDatefortheProject) and Max (EstimatedValidatedDate).  I also create a column for Dynamic Year based on Year Definition in Table A then create relationship between Table A and B using Year. However, my chart only refer to the month of ExecutionStartDatefortheProject, not the month between ExecutionStartDatefortheProject and EstimatedValidatedDate. My purpose is to showing monthly saving for each ID by devide the actual saving with month in year. Is that possible to do it?

Thank you

[rajendraongole1]

Hi @eryka_90 -I hope there is a relationship between Table A and DateTable on the Year column.

create a measure for monthly savings from actual saving and monts in year.

MonthlySaving =
DIVIDE (
SUM ( 'TableA'[Actual Saving ($)] ),
SUM ( 'TableA'[MonthsInYear] )
)

call the above monthly saving in return statement if the distribution fails. create a measure that distributes the savings across the months

TotalMonthlySaving =
VAR StartDate = MIN ( 'TableA'[ExecutionStartDatefortheProject] )
VAR EndDate = MAX ( 'TableA'[EstimatedValidatedDate] )
RETURN
SUMX (
FILTER (
DateTable,
DateTable[Date] >= StartDate && DateTable[Date] <= EndDate
),
[MonthlySaving]
)

use the monthly saving measure in your table chart it shows the distribution across the months. try the above approac and let knw.

Did I answer your question? Mark my post as a solution! This will help others on the forum!
Appreciate your Kudos!!

[eryka_90]

Hi @rajendraongole1 ,

I did try your approach but the value return is bigger.  I would like to create a chart as below

ID	Actual Saving ($)	Month in Year	*Average in Saving
123	20,700	8	2,587.5
123	10,500	12	875

For Year 2022, starting May until Dec. The chart should shows value $2,587.5 and 

For Year 2023, starting Jan until Dec. The chart should shows value $875.

and also when i create relationship between Table A and DateTable using Year, my chart only showing year up to 2022 only instead of full year which is my data have up to 2027.

Thank you