monthly percent

[RES]

I have data that looks like this. I would like a column table that shows the percent of each month including the month between.

PeakPct	TierStartDate	TierEndDate
100%	Sep 21	Sep 22
100%	Oct 22	May 23
50%	Jun 23	Dec 23
50%	Jun 23	May 24
25%	Jan 24	May 24
75%	Jun 24	Dec 24
25%	Jan 24	Dec 24
50%	Jan 25	Dec 25
25%	Jan 25	Dec 25
25%	Jan 25	Dec 25

Here is the code I have so far but it only shows the sum of percent for the month for certain months and not all months. 

Percent Each Month = CALCULATE(SUM('Client Hedges'[PeakPct]),

                          FILTER (  'Client Hedges',

                                    'Client Hedges'[TierStartDate].[Date]<= CALCULATE(MAX(CalendarS1[Date])) &&

                                     'Client Hedges'[TierStartDate] >= CALCULATE(MIN(CalendarS1[Date]))))

For instance Jan 24 should be the sum of 25% (Jan 24 - May 24), 25% (Jan 24 - Dec 24), and 50% (June 23 - May 24) totalling 100%.

[lbendlin · Accepted solution]

To report on things that are not there you need to use disconnected tables and/or crossjoins.  That includes a calendar table that needs to cover the entire period.

First, bring your data into a usable form

Then add the calendar, for example via

Dates = ADDCOLUMNS(CALENDAR("2021-09-01","2025-12-31"),"Year",FORMAT([Date],"yyyy"),"Month",FORMAT([Date],"mmm"),"MNo",FORMAT([Date],"mm"))

And then add a measure that checks if the date on the calendar overlaps with the date range in the percentage table.

[RES]

This is beautiful! Thank you!