Apply different date filters to specific column values in a pivot table

[tytds]

Suppose I have the following pivot table from one data source

The source data is essentially a spreadsheet of accounts which indicates 3 items (3 columns)- date account was created, date account was successful, date account was bought.

There are 3 stages an account goes through: step 1 it gets created. Step 2, if the employee deems the account to be successful, they mark it as successful and mark the date it became successful. If there is more interest in the account, the account is bought and the company receives money for it - they mark the date it was bought.

If you look at the data, the # of created accounts > # of successful accounts > # of bought accounts.

However, a created account can be made in Jan 2021 for example, but can be successful in Feb 2021 and then bought in June 2021. An account cannot necessarily be created, be successful, and be bought all in the same month/year.

That's the problem I have with this pivot table - the values field I have is using the count of date created, count of date successful and the columns on my pivot table is grouped by quarters of Y2021. The rows of the pivot table are the different regions the account belongs to. The problem I have is I am grouping the dates based on the date created and data is being filtered strictly from that date created filter; I don't know how to create a "global" date filter that is not strictly based on the date created filter

So, in the February 2021 column for example, it is telling me I have 8 created accounts and 6 successful accounts, when there really should be 5 successful accounts. If you double click that cell which launches a table of that data, you'll see an account that was successful in August 2021, however, it was created in February 2021, so that is why it will say 6 successful accounts . That is an example of a discrepancy in my pivot table. If region3 has 10 created accounts in January 2021 for example, and in the same month/year, has 5 successful accounts (by looking at the source data) and one account becomes successful in July 2021, the pivot table should indicate that correctly as 10 created accounts in Jan 2021 and 5 successful accounts in January 2021.

[#2]

@tytds 
not sure I understood entire logic. For such sample

- creating PivotTable add data to data model

- add Date (Calendar) table with PowerPivot or Power Query

- make relationship between it and main table on Date <=> Made

- add measures as

accounts made:=COUNTROWS ( Table1 )

accounts success:=VAR mindate =
    MIN ( 'Calendar'[Date] )
VAR maxDate =
    MAX ( 'Calendar'[Date] )
RETURN
    CALCULATE (
        COUNTA ( Table1[Success] ),
        'Calendar'[Date] >= mindate
            && 'Calendar'[Date] <= maxDate
    )


accounts bought:=VAR mindate =
    MIN ( 'Calendar'[Date] )
VAR maxDate =
    MAX ( 'Calendar'[Date] )
RETURN
    CALCULATE (
        COUNTA ( Table1[Bought] ),
        'Calendar'[Date] >= mindate
            && 'Calendar'[Date] <= maxDate
    )

[#3]

@SergeiBaklan 

The columns would be months on the pivot table and there would be different years as the row labels

Also, your pivot table above is not accurate (there should be 0 successful accounts in Jan 2021)

[#4]

corect

[#5]

hmm i thought this would be an easier process. I don't want to play around with too much code but this date discrepancy is giving me a problem and I can't present my report accurately.

[#6]

@tytds 

If to continue it's better if you generate small sample file to illustrate the logic manually adding desired result. That's 5-10 minutes job, your actual data is not required.

[#7]

Here is a sample of the source data
https://easyupload.io/zlogle

[#8]

@tytds 
thank you, but that doesn't explain the logic. In sample file we have 12 accounts submitted in January. Each of them has Date Successful, thus all 12 of January accounts are successful.

Or you mean count successful accounts only if Date Successful is in January, doesn't matter when they were submitted?

Or some other logic?

[#9]

When you setup a pivot table, there should be number of submitted accounts as a column, number of successful accounts in another column, and the hit ratio (simple formula successful/submitted). The number of submitted accounts in January should be 12. There should be a "sub column" for months. The number of successful accounts in the January column should be 4, meaning, those accounts turned successful in January. The number of successful accounts in March column (regardless when they were submitted) should be 2

[#10]

@tytds 

With that you need to create separate Date (aka Calendar) table. That could be done by Power Query or directly in PowerPivot. Make relationships with main table and hide date fields in the latest.

Create measures

Submitted:=CALCULATE (
    COUNTROWS ( Range ),
    USERELATIONSHIP ( 'Calendar'[Date], Range[Date Submitted] )
)

and

Successed:=CALCULATE (
    COUNTROWS ( Range ),
    USERELATIONSHIP ( 'Calendar'[Date], Range[Date Successful] )
)

use them in PivotTable

[#11]

Is there a tutorial on creating the relationships from the first picture? I'm not familiar with creating the measures either

[#12]

@tytds 

You may start from

Relationships between tables in a Data Model (microsoft.com)

QuickStart: Learn DAX Basics in 30 Minutes (microsoft.com)