Forum Discussion
monthly percent
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.
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%.
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.
2 Replies
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.
This is beautiful! Thank you!
