topic Re: Seeking assistant and suggestion to create a monthly chart between two dates in DAX Commands and Tips

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

eryka_90 — Tue, 18 Jun 2024 06:43:43 GMT

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

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

rajendraongole1 — Tue, 18 Jun 2024 08:13:10 GMT

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!!

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

eryka_90 — Wed, 19 Jun 2024 01:31:46 GMT

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