KPI for Past & Upcoming Dates

[Alta88]

I'm working on a KPI project that requires a chart showing how many homes are closing in each month. The idea is to display the Month number and Year Number for every month going back 12 months and going forward 6 months. (36 columns).

Here is a rough idea of what this should look like: 

Month Number	Month	Year	Closing Date	Account Number	Total Accounts
1	January	2021
2	February	2021
3	March	2021
4	April	2021
5	May	2021
6	June	2021
7	July	2021
8	August	2021
9	September	2021
10	October	2021
11	November	2021
12	December	2021
1	January	2022
2	February	2022
3	March	2022
4	April	2022
5	May	2022
6	June	2022
7	July	2022
8	August	2022
9	September	2022
10	October	2022
11	November	2022

Obviously there would be several accounts and dates within each month too. My starting month/date would be May in this case so I've posted that range in the table above. What DAX formulas could accomplish this? 

[Alta88]

Yes, here's some mock data of the table I'm working with. I'm sending you a list of the closing home IDs along with their associated closing dates. 

CustomerID_Txt	Expected Closing Date
4532154	6/11/2022
1588673	6/11/2022
2159783	6/13/2022
1687532	5/21/2022
5487621	5/21/2022
7954324	5/21/2022
2581496	9/1/2022
1479631	10/3/2022
3697521	7/21/2022
8527413	8/1/2022
3219874	8/1/2022
1591237	8/1/2022
9875431	8/26/2022
4865431	8/10/2022
8916132	9/1/2022
1598761	9/14/2022
1675389	10/6/2022
6457183	9/30/2021
7851432	6/30/2021
3185741	8/25/2021

As for the output, I'm not quite sure if we're going to do a slicer, although that's a great idea. For now we just want a standard table showing Month (number), Year, and Closing Count. 

[v-jingzhang · Accepted solution]

Hi @Alta88 

You can create two columns in your table. 'Closing Data' is my table name, you can change it according to your table.

Year = YEAR('Closing Data'[Expected Closing Date])

Month = MONTH('Closing Data'[Expected Closing Date])

Then go to report canvas, add a Table visual to the report. Select Year, Month and CustomerID_Txt columns to display in the table visual. In Columns well, right click on Year and select Don't summarize for it. Also select Don't summarize for Month. Select Count for CustomerID_Txt. Now the CustomerID_Txt column will have a column name "Count of xxxxxxx". You can use "Rename for this visual" option to change it to "Closing Count". 

Hope this helps. 

Best Regards,
Community Support Team _ Jing
If this post helps, please Accept it as Solution to help other members find it.

[Alta88]

Thanks, Jing. This definitely worked. Now I'm verifying my total closings per each month. Something is off because PBI is showing count of total closings/accounts in May 2022 was 6,513, but when I downloaded it to Excel and counted it came to 4,744. Do I need to change the way the program is counting? Why is there such a discrepancy?

[v-jingzhang]

Hi @Alta88 

Do some CustomerIDs have multiple Expected Closing Dates in a month? What do you download to Excel and how do you count it in Excel? If you want to count the number of distinct customer IDs, you can try Count (Distinct) aggregation type. 

[Alta88]

So I was able to verify a few things and it looks like the totals are accurate; the other datasets I was comparing them too were including other points that are not relevant to my table. Thanks for all the help!

[Alta88]

I've built columns looking back in time but once I get to 6 months ago (December 2021) it won't calculate. What am I missing?

[Alta88]

Thanks Miguel. Looks like I got some clarification from the boss about what is needed. This is what he has in mind. So he wants me to build columns in the background to then arrive at a table that looks like the one below. 

[MFelix]

Hi @Alta88 ,

Do you want to show the values based on the selection date of the and then present the value within the time range you have refer?

Believe you need to have a disconnected calendar table to get the slicer for the date selection.

Then you need to add something similar to this:

Houses =
COUNTROWS (
    FILTER (
        Table,
        Table[Date] >= DATEADD ( SlicerTable[Date], -12, MONTH )
            && Table[Date] <= DATEADD ( SlicerTable[Date], 6, MONTH )
    )
)

[Alta88]

Thanks, Miguel. 

Where it lists, 'table', is this referring to a table in my data set or is this just the word I use in the formula?

[MFelix]

Hi @Alta88 ,

It's the table on your model you need to replace it by the correct names.

[v-jingzhang]

Hi @Alta88 

Can you please provide some mock data of the original table you have? Do you already have the closing count number for every month in your table, or do you have a list of closing home IDs (or names) and their closing dates in your table? 

And for the expected output, do you want to have a slicer to pick a month, then go back/forward from that month? Or go back/forward from the current month (always based on today)?

Regards,
Community Support Team _ Jing