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

[DemoFour]

Hi people. 

Is there anyone who really knows how to code in DAX, who would be willing to speak to me about a complex problem I am trying to solve.  I have done lot's of searching, but what I really need is to sit and talk about how I can reach the output with someone who really understands DAX. 

Happy to show more data and the model, if you would like to help. 

thanks 

[DemoFour · Accepted solution]

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. 

[FlipFlop1]

Does this help?

Given a table with Days Open as an example, you can add the measure (obviously for the first 12 months, there is no full historical data).

Last 365 Days = CALCULATE(

   SUMX('Days Car Open', 'Days Car Open'[Days Car Open]),

    DATESINPERIOD(

        'Date'[Date],

        LASTDATE('Date'[Date]), -12, MONTH)

    )

[DemoFour]

Hay @FlipFlop1 

Thank you for posting up, that is a route I have explored, but it still gives the same output within the filter context of the model, as each audit has a date this is then represented in the table by that date. 

I have years worth of data I am just using a very slimmed down selection of the data to test out the build with. 

The output I need is the Total of this measure output and then this is added to the Month.  SO the total of that measure is the single value of the month and previous 11 months as a total added to the single "cell" marked with the month name. 

I am thinking that actually I need to go back and create this in M from the source data as a table that will only ever give the current month, and the previous 11 months and SUM all the days in each period and then add them to that current month name that then it's self roles back 12 months continuously.  

[Anonymous]

Thanks for the reply from lbendlin and FlipFlop1  , please allow me to provide another insight:

Hi, @DemoFour 

Regarding the issue you raised, my solution is as follows:

1.First I have created the following table and the column names and data are the data you have given:

2. Below are the measure I've created for your needs:

MEASURE = 
VAR cd1 =
    MAX ( 'Table'[Date] )
RETURN
    CALCULATE (
        SUM ( 'Table'[days open] ),
        FILTER (
            ALL ( 'Table' ),
            'Table'[Date] <= cd1
                && 'Table'[Date] >= EDATE ( cd1, -12 )
        )
    )

3.Here's my final result, which I hope meets your requirements.

Please find the attached pbix relevant to the case.

Best Regards,

Leroy Lu

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[DemoFour]

Hi @Anonymous 

Thank you for the solution you offered, what I did in the end was connect to the dynamics environment with a SQL connection in Power Query writing the SQL as part of the connection,  to create the table and to complete this on a dynamic 12 month rolling basis within SQL. 

This table is then used to create the visual from the table I created as a disconnected table - as I do not need it to move around with the data in the data model. 

If anyone is interested, I am happy to share the SQL query. 

Thank you again for looking at this issue and posting up the DAX for a potential resolve. 

[Anonymous]

Hi, @DemoFour 

Thank you for your swift response.

We are delighted to hear that your issue has been resolved. If you have the time, we would appreciate it if you could share your SQL query and mark your response as the accepted solution.

This is crucial for the growth of our community, as it can help other members with similar issues find solutions more quickly.

Thank you in advance for your contribution.

If there is anything else we can assist you with, please feel free to contact us.

Best Regards,

Leroy Lu

[lbendlin]

This here is a forum where users help users, time permitting.  For more complex requests contact a Microsoft partner near you.

Please provide sample data that covers your issue or question completely, in a usable format (not as a screenshot).

Do not include sensitive information or anything not related to the issue or question.

If you are unsure how to upload data please refer to https://community.fabric.microsoft.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-...

Please show the expected outcome based on the sample data you provided.

Want faster answers? https://community.fabric.microsoft.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447...

[DemoFour]

Thanks for the advice @lbendlin  I have already posted this up, but here goes again with the issue 🙂

The output is a total for the previous 12 months Total days open, based on the current month.  Giving the column the value of the current month and the previous eleven months before this, then adding another column to designate this September 2024.  Each month of the year gets the previous total for the proceeding 11 months so the output is this: 

Resulting Table

Date	Sum of days open
Jan 23	26958
Feb	27021
Mar	21769
Apr	21456
May	19486
Jun	14889
Jul	14240
Aug	14449
Sep	10661
Oct	11239
Nov	11435
Dec	8841
Jan 24	8841
Feb	8223
Mar	8464
Apr	7924
May	5620
Jun	5499
Jul	4848
Aug	2156
Sep
Oct
Nov
Dec

The sample data is as follows 
Audits = 1 Item = N for each one so there is a range of days

Audit ID	Audit Conducted Date	Completion Date	Item ID	Days Open
1	8/2/2022	9/15/2022	1	44
15	6/6/2023	6/28/2023	1	22
54	4/21/2024		4	124

The days open is in M so that if the completed date is blank then TODAY is used to count the days open. 

The model has date table, and this is set up properly. 

I can compute the individual days open, what I need to do is take the next step to create the virtual table to use in a visual that looks like this:

Total days is a measure SUM( days Open)

The model will filter any combinations of DAX to get the total by the individual dates, so I need the total for the month and it's corresponding previous 11 months to make the table total as the output. 

If you need any more information then happy to share 🙂 

[lbendlin]

 to create the virtual table to use in a visual that looks like this:

Virtual tables cannot create charts.  Their life is confined to the bowels of the measure they are used in. 

I have a hard time correlating your sample data to the desired outcome. Feels like some data and context is missing?

[DemoFour]

Morning @lbendlin 

Maybe that's the wrong language to say virtual table. 

The customer each month, takes a spreadsheet, filters to the allocated months and then sums the output, and adds this to the table with the date as per the output, they want an average days, so the other column is a count of the events in the filter context.  Then this is divided with the days open total.

There is another thread with more data, for some reason I could not populate the table on my OG post.  This is why I wanted to speak to someone, as people are struggling to get an output from a manual process in Power BI.  I am happy to take the table in M and I think that is the route I will have to take and not in DAX.

The values in the output table is from the sample spreadsheet made by the customer as they showed me what they do each month and how they get the numbers.  I will look at posting a file, but my org blocks almost everything leaving the tenant

I am also happy to share my DAX, the issue is the model filters by the dates and I need to create that rolling table, that has the Total days in the previous 12 months against each month of the ear looking back, so I can count the items and then do the divide. 

the output in the table above that I need would be May 2024 = 19486