Help with creating a virtual table for previous year total allocated to the current month in context

good morning Anonymous 

I would be happy to share how I solved this problem with SQL. 

As I can connect into Dynamics with Read only acsess with SSMS it is a bit more complex, you could of course do this in the Query Editor in M, but I think it would be a bit longer to compute the result, but I have not tested this thory.

I can connect to the SQL instance in Dynamics to acsess the Dataverse tables, by connecting and then adding this SQL query to create the table I needed in the connection.

-- Declare variables for repeated calculations
DECLARE @CurrentDate DATE = CAST(GETDATE() AS DATE); 
DECLARE @StartDate DATE = DATEADD(month, -11, DATEADD(month, DATEDIFF(month, 0, @CurrentDate), 0)); -- this will create the 11 months before the current month to return only completed months
DECLARE @PreviousStartDate DATE = DATEADD(month, -23, DATEADD(month, DATEDIFF(month, 0, @CurrentDate), 0)); -- this will give us the data in the 23 month window we need for all the values in the windows

-- Select the final results with the desired columns, including months without data
SELECT 
    CASE 
        WHEN FORMAT(DATEADD(month, -n.n, @CurrentDate), 'MM') = '01' 
        THEN FORMAT(DATEADD(month, -n.n, @CurrentDate), 'MMM yyyy')
        ELSE FORMAT(DATEADD(month, -n.n, @CurrentDate), 'MMM')
    END AS Month,  -- Generate a list of months with correct year for January

    -- Add a sort order column
    n.n AS SortOrder,

    COALESCE(SUM(PreviousMonths.CountOfCAR_ID), 0) AS [Total CAR],  -- Sum of CAR_ID counts over the rolling window 
    COALESCE(SUM(PreviousMonths.SumOfDaysOpen), 0) AS [Total Days Open],  -- Sum of DaysOpen over the rolling window
    CAST(
        ROUND(
            CASE 
                WHEN COALESCE(SUM(PreviousMonths.CountOfCAR_ID), 0) = 0 THEN 0  -- Avoid division by zero
                ELSE COALESCE(SUM(PreviousMonths.SumOfDaysOpen), 0) * 1.0 / COALESCE(SUM(PreviousMonths.CountOfCAR_ID), 0)  -- Calculate average days
            END, 0  -- Round to the nearest whole number
        ) AS INT  -- Cast the result to an integer to remove trailing zeros
    ) AS [Average Days Open]  -- The average days column
FROM 
    (
        -- Generate a list of numbers from 0 to 11 to represent months
        SELECT 0 AS n UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL
        SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL
        SELECT 8 UNION ALL SELECT 9 UNION ALL SELECT 10 UNION ALL SELECT 11
    ) AS n

LEFT JOIN (
    -- Subquery to get the data for the current month and the previous 11 months
    SELECT 
        FORMAT(b.new_auditconducteddate, 'yyyy-MM') AS Month,  -- Format the audit conducted date to 'yyyy-MM'
        COUNT(a.new_responseid) AS CountOfCAR_ID,  -- Count of CAR_ID for the month
        SUM(DATEDIFF(day, b.new_auditconducteddate, COALESCE(a.new_completiondate, @CurrentDate))) AS SumOfDaysOpen  -- Sum of DaysOpen for the month using Effective Date
    FROM 
        [dbo].[new_auditresponse] a -- This is the tables with the CARs in
    INNER JOIN 
        [dbo].[new_audit] b -- This is the Audit table we need to join these tables as there is no start date for the CAR so we substitute this with the Audit Conducted date
    ON 
        a.cr5cd_auditid = b.new_audit_id
    WHERE 
        b.new_auditconducteddate >= @StartDate  -- Filter for the last 12 months
    GROUP BY 
        FORMAT(b.new_auditconducteddate, 'yyyy-MM')  -- Group by month
) AS CurrentMonth
ON FORMAT(DATEADD(month, -n.n, @CurrentDate), 'yyyy-MM') = CurrentMonth.Month

LEFT JOIN (
    -- Subquery to get the data for the previous 12 months
    SELECT 
        FORMAT(b.new_auditconducteddate, 'yyyy-MM') AS Month,  -- Format the audit conducted date to 'yyyy-MM'
        COUNT(a.new_responseid) AS CountOfCAR_ID,  -- Count of CAR_ID for the month
        SUM(DATEDIFF(day, COALESCE(b.new_auditconducteddate, @CurrentDate), COALESCE(a.new_completiondate, @CurrentDate))) AS SumOfDaysOpen  -- Sum of DaysOpen for the month using Effective Date
    FROM 
        [dbo].[new_auditresponse] a
    INNER JOIN 
        [dbo].[new_audit] b 
    ON 
        a.cr5cd_auditid = b.new_audit_id
    WHERE 
        b.new_auditconducteddate >= @PreviousStartDate  -- Filter for the last 24 months
    GROUP BY 
        FORMAT(b.new_auditconducteddate, 'yyyy-MM')  -- Group by month
) AS PreviousMonths
ON FORMAT(DATEADD(month, -n.n, @CurrentDate), 'yyyy-MM') >= PreviousMonths.Month  -- Join condition to include current and previous months
AND FORMAT(DATEADD(month, -n.n, @CurrentDate), 'yyyy-MM') < FORMAT(DATEADD(month, 12, CAST(PreviousMonths.Month + '-01' AS DATE)), 'yyyy-MM')  -- Ensure the rolling window includes the previous 11 months

WHERE 
    DATEADD(month, -n.n, @CurrentDate) < DATEADD(month, DATEDIFF(month, 0, @CurrentDate), 0)  -- Exclude the current month if not complete

GROUP BY 
    CASE -- this part is to meet the customer requirements of having the short month and January to have the year, it also need to be dynamic as Jan moves through the table.
        WHEN FORMAT(DATEADD(month, -n.n, @CurrentDate), 'MM') = '01' 
        THEN FORMAT(DATEADD(month, -n.n, @CurrentDate), 'MMM yyyy')
        ELSE FORMAT(DATEADD(month, -n.n, @CurrentDate), 'MMM')
    END,  -- Group by the generated months list
    n.n  -- Include the sort order in the group by clause
ORDER BY 
    n.n  DESC-- Order the results by the sort order column

 
Now I am not a SQL developer and I am a business Power BI user with 6 years experience and I have taught myself SQL, so please don't judge and happy to see how this could have been done in a more optimised way. 

This is the output

Then I just create the line chart, as I do not need this to be affected by anything else in the report, this is ok as a table for just the visual.