First, thank you very much for the detailed explanation of the problem, and for posting a PBIX file. That’s an excellent start to getting the best response/support on the forum.
Before jumping into any DAX work, I always like to confirm two things:
is the data model set up correctly? – Yes. You’ve got a good, simple structure - date table, dimension table linked to fact table with one to many relationships. Check.
are the data tables set up appropriately for the analysis that needs to be done - unfortunately, as you suspected above the answer here is no. The “short and wide” structure (relatively few rows, lots of columns) is the exact opposite of what works best in most cases for DAX/Power BI. The individual month columns you have in your fact table are going to make it very difficult at best to get your DAX to work out properly.
Before we dive into fixing the DAX, we’re going to need to work on restructuring your fact table to reshape it into a “long and narrow” structure. To do that in Power Query, we’ll need your Excel data file. Here are a couple of recent threads discussing the short/wide versus long/narrow restructuring.
Also, please take a look at the following regarding your message to @Greg about response time on the forum:
