Solved: Time intelligence measures

Anonymous · ‎01-08-2020

Hi all,

I'm hoping someone can help. I need to create some time intelligence measures.

I have a large data table (10,000+ rows) of information relating to customer support documents, and how often each document has been used in a support scenario. Rows include the customer name for each document, the RefNum for the support ticket that has resulted from each support scenario (RefNum does not contain unique numbers, because a customer support document might have been used in multiple different support scenarios. So each row in the table represents a single support scenario), the Full Name of the support analyst who handled the case, the timestamp 'DateActivityCreated' of when the case was generated, and the title of the support document. Example below:

Tenant	RefNum	Full Name	DateActivityCreated	Doc Title
Google	9193141	Jenny Jenson	2/12/2019 14:18	Google - Pin@store Error - Card could not be written or read
Alphabet	9193142	Jenny Jenson	14/01/2020 14:16	Alphabet - Application - Adobe products license issue
Apple	9193144	Jenny Jenson	2/12/2019 14:18	Apple - Password Reset - NPE
Google	9193145	Jenny Jenson	2/12/2019 14:19	Google - Postlink - Freezing in Parcel Value Field
IBM	9193147	Jimmy James	2/12/2019 14:17	IBM - Network (LAN) Account Unlock
IBM	9193147	Jimmy James	2/12/2019 14:18	IBM - Network (LAN) Account Unlock
IBM	9193147	Jimmy James	2/12/2019 14:18	IBM - Network (LAN) Account Unlock
IBM	9193147	Cameron Camson	2/12/2019 14:16	IBM - Network (LAN) Account Unlock
IBM	9193147	Jenny Jenson	14/01/2020 14:16	IBM - Network (LAN) Account Unlock
IBM	9193147	Jimmy James	14/01/2020 14:16	IBM - Network (LAN) Account Unlock
IBM	9193147	Jimmy James	14/01/2020 14:16	IBM - Network (LAN) Account Unlock
IBM	9193147	Jimmy James	14/01/2020 14:16	IBM - Network (LAN) Account Unlock
IBM	9193147	Jimmy James	14/01/2020 14:16	IBM - Network (LAN) Account Unlock
ICC	9193151	Cameron Camson	2/12/2019 14:19	ICC - eShip - Child Accounts
Alphabet	9193152	Cameron Camson	2/12/2019 14:18	Alphabet - JDE - Unable to save the report because the network drive is missing
Samsung	9193154	Jackie Jackson	14/01/2020 14:16	Samsung - New user request
Samsung	9193154	Jackie Jackson	2/12/2019 14:18	Samsung - New user request
Samsung	9193154	Jackie Jackson	2/12/2019 14:18	Samsung - New user request
Samsung	9193154	Jackie Jackson	2/12/2019 14:18	Samsung - New user request
Samsung	9193154	Jackie Jackson	2/12/2019 14:18	Samsung - New user request
Samsung	9193154	Jackie Jackson	2/12/2019 14:18	Samsung - New user request
Google	9193168	Jackie Jackson	2/12/2019 14:22	Google - NZTA TXN Error - 953 timed out waiting for response from CDI
Blackberry Limited	9193172	Jimmy James	2/12/2019 14:21	Blackberry Limited – Email – Outlook Issues (Generic)
Blackberry Limited	9193172	Jimmy James	14/01/2020 14:16	Blackberry Limited – Email – Outlook Issues (Generic)
Blackberry Limited	9193172	Jimmy James	2/12/2019 14:21	Blackberry Limited – Email – Outlook Issues (Generic)
Samsung	9193174	Jimmy James	2/12/2019 14:21	Samsung - Disable user request
ICC	9193175	Jimmy James	2/12/2019 14:22	ICC - eShip - Printer Issues (newly onboarded user)
Alphabet	9193177	Jenny Jenson	2/12/2019 14:21	Alphabet - Forcepoint - Email Releases
Alphabet	9193177	Jenny Jenson	14/01/2020 14:16	Alphabet - Forcepoint - Email Releases

I'd like to be able to create a summary table, that shows a unique list of the support documents, the customer name for each document, and some measures. Here's an example (the columns in bold are the ones I need):

Customer	DocTitle	Count Links (Last Month)	Count Links (Last Month -2)	% Change From (Last Month -2)	Rank (Last Month)	Rank (Last Month -2)	Rank Change (From Last Month-2 to Last Month)
IBM	IBM - Network (LAN) Account Unlock	5	4	25%	1	2	-1
Alphabet	Alphabet - Application - Adobe products license issue	1	0	New	2	N/A	2
Samsung	Samsung - New user request	1	5	-80%	2	1	-1
Blackberry Limited	Blackberry Limited – Email – Outlook Issues (Generic)	1	2	-50%	2	3	-1
Alphabet	Alphabet - Forcepoint - Email Releases	1	1	0%	2	4	-2
Google	Google - Pin@store Error - Card could not be written or read	0	1	-100%	N/A	4	-4
Apple	Apple - Password Reset - NPE	0	1	-100%	N/A	4	-4
Google	Google - Postlink - Freezing in Parcel Value Field	0	1	-100%	N/A	4	-4
ICC	ICC - eShip - Child Accounts	0	1	-100%	N/A	4	-4
Alphabet	Alphabet - JDE - Unable to save the report because the network drive is missing	0	1	-100%	N/A	4	-4
Google	Google - NZTA TXN Error - 953 timed out waiting for response from CDI	0	1	-100%	N/A	4	-4
Samsung	Samsung - Disable user request	0	1	-100%	N/A	4	-4
ICC	ICC - eShip - Printer Issues (newly onboarded user)	0	1	-100%	N/A	4	-4

And here's how the fields would be calculated:

1) Count Links (Last Month) = count of how many times each support document has been used (based on how many rows it appears in, in the month preceeding whatever month it is currently)
2) Count Links (Last Month -2) = count of how many times each support document has been used (based on how many rows it appears in, in the month BEFORE the month preceeding whatever month it is currently)
3) % Change From (Last Month -2) = This would be calculated as (#3-#2)/#2, except where #2 = 0, in which case the result should be "New"
4) Rank (Last Month) = for the top 20 support documents only, this will show a ranking based on #1
5) Rank (Last Month -2) = for the top 20 support documents only, this will show a ranking based on #1 (in the month BEFORE the month preceeding whatever month it is currently)
6) Rank Change (From Last Month-2 to Last Month) = the difference between #4 and #5

Hope that all makes sense. Any help would really be appreciated!

v-alq-msft · ‎01-09-2020

Hi, @Anonymous

Based on your description, I created data to reproduce your scenario. You may create a calculated table as follows. I used "Date(2020,1,1)" as the current date for testing. You may replace it with Now().

Summary = 
SUMMARIZE(
          'Table',
          'Table'[Tenant],
          'Table'[Doc Title],
          "Count Links (Last Month)",
          CALCULATE(
                   COUNTROWS('Table'),
                   FILTER(
                        'Table',
                        AND(
                            'Table'[DateActivityCreated]<=EOMONTH(DATE(2020,2,1),-1),
                            'Table'[DateActivityCreated]>=EOMONTH(DATE(2020,2,1),-2)+1
                        )
                   )
          ),
          "Count Links (Last Month -2)",
          CALCULATE(
                   COUNTROWS('Table'),
                   FILTER(
                        'Table',
                        AND(
                            'Table'[DateActivityCreated]<=EOMONTH(DATE(2020,2,1),-2),
                            'Table'[DateActivityCreated]>=EOMONTH(DATE(2020,2,1),-3)+1
                        )
                   )
          )

)

Here are the measures.

% Change From (Last Month -2) = 
var _lastmonth2 = SUM(Summary[Count Links (Last Month -2)])
var _lastmonth = SUM(Summary[Count Links (Last Month)])
return
IF(
    _lastmonth2 <> 0,
    SUMX(
         Summary,
         (_lastmonth-Summary[Count Links (Last Month -2)])/Summary[Count Links (Last Month -2)]
    ),
    "New"
)

Rank (Last Month) = 
RANKX(
    ALLSELECTED(Summary),
    CALCULATE(SUM(Summary[Count Links (Last Month)])),
    ,
    DESC,
    Dense
)

Rank (Last Month -2) = 
RANKX(
      ALLSELECTED(Summary),
      CALCULATE(SUM(Summary[Count Links (Last Month -2)])),
      ,
      DESC,
      Dense
)

Rank Change (From Last Month-2 to Last Month) = [Rank (Last Month)]-[Rank (Last Month -2)]

Then you may go to the table level filter and configure both the values of Rank(Last Month) and Rank(Last Month - 2) with 'is less than or equal to 20'.

Result:

Best Regards

Allan

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

View solution in original post

v-alq-msft · ‎01-16-2020

Hi, @Anonymous

If you take the answer of someone, please mark it as the solution to help the other members who have same problems find it more quickly. If not, let me know and I'll try to help you further. Thanks.

Best Regards

Allan

Anonymous · ‎01-19-2020

Apologies @v-alq-msft, I forgot to do that - thanks for following it up for me!

v-alq-msft · ‎01-09-2020

Hi, @Anonymous

Based on your description, I created data to reproduce your scenario. You may create a calculated table as follows. I used "Date(2020,1,1)" as the current date for testing. You may replace it with Now().

Summary = 
SUMMARIZE(
          'Table',
          'Table'[Tenant],
          'Table'[Doc Title],
          "Count Links (Last Month)",
          CALCULATE(
                   COUNTROWS('Table'),
                   FILTER(
                        'Table',
                        AND(
                            'Table'[DateActivityCreated]<=EOMONTH(DATE(2020,2,1),-1),
                            'Table'[DateActivityCreated]>=EOMONTH(DATE(2020,2,1),-2)+1
                        )
                   )
          ),
          "Count Links (Last Month -2)",
          CALCULATE(
                   COUNTROWS('Table'),
                   FILTER(
                        'Table',
                        AND(
                            'Table'[DateActivityCreated]<=EOMONTH(DATE(2020,2,1),-2),
                            'Table'[DateActivityCreated]>=EOMONTH(DATE(2020,2,1),-3)+1
                        )
                   )
          )

)

Here are the measures.

% Change From (Last Month -2) = 
var _lastmonth2 = SUM(Summary[Count Links (Last Month -2)])
var _lastmonth = SUM(Summary[Count Links (Last Month)])
return
IF(
    _lastmonth2 <> 0,
    SUMX(
         Summary,
         (_lastmonth-Summary[Count Links (Last Month -2)])/Summary[Count Links (Last Month -2)]
    ),
    "New"
)

Rank (Last Month) = 
RANKX(
    ALLSELECTED(Summary),
    CALCULATE(SUM(Summary[Count Links (Last Month)])),
    ,
    DESC,
    Dense
)

Rank (Last Month -2) = 
RANKX(
      ALLSELECTED(Summary),
      CALCULATE(SUM(Summary[Count Links (Last Month -2)])),
      ,
      DESC,
      Dense
)

Rank Change (From Last Month-2 to Last Month) = [Rank (Last Month)]-[Rank (Last Month -2)]

Then you may go to the table level filter and configure both the values of Rank(Last Month) and Rank(Last Month - 2) with 'is less than or equal to 20'.

Result:

Best Regards

Allan

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

Anonymous · ‎01-09-2020

@v-alq-msft: Hi Allan, that's perfect 🙂 Thank you so much for the detailed response, I really appreciate it 🙂

Time intelligence measures

Helpful resources

Power BI Dataviz World Championships

Power BI Monthly Update - December 2025

FabCon Atlanta 2026

How to Get Your Question Answered Quickly

FabCon is coming to Atlanta

Time intelligence measures

Helpful resources

Power BI Dataviz World Championships

Power BI Monthly Update - December 2025

FabCon Atlanta 2026

How to Get Your Question Answered Quickly