Cumulative monthly target with filtered dates

[inesj]

Hello everyone,

I am quite new to PowerBI and would very much appreciate your help on something I have been stuck on for a few days now.

I have an order table (let's call it "Orders") with a transaction date, the assigned contract for that order, as well as the start and end dates of that contract.

I have another table ("Contracts") with the contract name and the orders limit for that contract (a limit of 29, for example, means that I can only place 29 orders against that contract throughout its whole duration).

These two tables have a many-to-one relationship from the Orders table to the Contracts table, based on the contract name.

I have a slicer that allows the user to select a contract and a year, and see all orders information for this contract during that year.

This slicer "controls" a chart that shows, for each month where there were orders against the selected contract during the selected year, the cumulative number of orders. I linked the table to a "Months1" table listing all 12 months names (by creating a "Transaction Month" calculated column in the Orders table and using it as the common column), and then used these months as the X-Axis, so that even months with no order data are shown. 

So far, so good: 

Note: There was 1 order in June and 5 in July, so the cumulative quantity in July is indeed 6.

Now, however, I would like to add a line to that same chart that shows the cumulative order limit of the contract. If the contract starts in April 2020 and ends in March 2021, for example, and has a limit of 29, I would like to create a line in the 2020 chart that starts at 2.42 in April (29/12 months), and reaches 21.75 in December. In the 2021 chart, it would start at 24.17 in January and reach its end (29) in March. This would enable users to evaluate their cumulative monthly consumption against the limit.

I have played around a bit and created a "MonthsYear" table that uses the filters applied to the Orders table (namely the contract) and shows the cumulative monthly limit during the contract's duration:

MonthYear =

var FullCalendar = ADDCOLUMNS(CALENDAR(MIN(Orders[StartDate]),MAX(Orders[EndDate])),"Year",YEAR([Date]),"Month",MONTH([Date]),"Month Name",FORMAT(([Date]),"MMMM"),"MonthYear#",MONTH([Date])& "-" & YEAR([Date]))

RETURN

SUMMARIZE(FullCalendar,[Month],[Month Name],[Year],[MonthYear#])

MonthlyLimit =

CALCULATE(IF(MIN(MonthYear[MonthYear#]) >= MIN(Orders[StartDate]) && MIN(MonthYear[MonthYear#) <= MAX(Orders[EndDate]),SUM(Contracts[Limit])/(DATEDIFF(MIN(Orders[StartDate]),MAX(Orders[EndDate]),MONTH)+1),0), FILTER(ALLSELECTED(Orders[Contract]),MIN(MonthYear[Year]) >= YEAR(MIN(Orders[StartDate])) && MIN(MonthYear[Year]) <= YEAR(MAX(Orders[EndDate]))))

MonthlyCumulLimit =

CALCULATE(IF(MIN(MonthYear[MonthYear#]) >= MIN(Orders[StartDate]) && MIN(MonthYear[MonthYear#) <= MAX(Orders[EndDate]),MonthYear[MonthlyLimit]*(DATEDIFF(MIN(Orders[StartDate]),MIN(MonthYear[MonthYear#]),MONTH)+1),0),FILTER(ALLSELECTED(Orders[Contract]), MIN(MonthYear[Year]) >= YEAR(MIN(Orders[StartDate])) && MIN(Orders[Year]) <= YEAR(MAX(Orders[EndDate]))))

If I link this new table to a "Months2" table that lists the 12 months names (here again taking "Month Name" as the common column), and create a new line chart with these months in the X-Axis and my MonthlyCumulLimit as Line Values, it shows me exactly what I am looking for: 

 

But now, if I try to merge these two charts (the Orders column chart and the Target line chart) by linking them both to the same "Months" table, I get the following: 

I see 3 issues:

1. It seems like when there is no order data, the cumulative target values are also assumed to be 0 (whereas they looked just fine when the order data was not included).
2. The chart does not recognize the transaction year of an order (the June and July order info should be shown for 2020 only).
3. The "Year" filter also suggests Years where there is no value:

I would like to only see the years where there are values - in other words, the years between the contract start and end dates. 

I guess this is all a problem of filtering within my queries, but I cannot seem to figure out where the error could be...

Your help would be very much appreciated!

Please let me know if anything was unclear, or if you need any more information to support me.

Many thanks in advance!

IJ.

[AllisonKennedy]

@inesj Do you have a proper dimDate table? Or just MonthYear tables? You need a DimDate table, then create the measure based on that and connect all data to that single data table. Use the Date table in all slicers and in the axis for the visual. https://excelwithallison.blogspot.com/2020/04/dimdate-what-why-and-how.html 

[inesj]

Hello Allison,

Many thanks for your quick reply.

I have created a DimDate table and based my measures on it, but unfortunately it is still not working. 

Everything starts well, but "breaks" as soon as I connect my DimTable to my Orders table. I have created a Matrix to show you the behaviors:
Before the relationship is created:

After the relationship is created:

It really looks like there is something wrong with my CumulMonthlyTarget measure, which seems to disregard all the months with no "CumulativePartsUsage" (cumulative Orders) data.

The measure is the following:

CumulMonthlyTarget =

CALCULATE(IF(MIN(DimDate[Date]) >= MIN(Orders[StartDate]) && MIN(MIN(DimDate[Date]) <= MAX(Orders[EndDate]),MonthYear[MonthlyLimit]*(DATEDIFF(MIN(Orders[StartDate]),MIN(MIN(DimDate[Date]),MONTH)+1),0),FILTER(ALLSELECTED(Orders[Contract]), MIN(DimDate[Year]) >= YEAR(MIN(Orders[StartDate])) && MIN(DimDate[Year]) <= YEAR(MAX(Orders[EndDate]))))

I highly suspect the error lies in my "IF" statement, but I cannot figure out how I should change it so that it works.
Do you maybe have an idea?

Thanks again for your help.