Simple measure

[Anonymous]

Hi,

I want a measure that gives me the count of Client ID that have made a transaction in the last 3 months. My table looks like this:

Client ID	Operation	Date
1	Last transaction	8/30/2019
2	Opening	8/30/2019
3	Closure	8/30/2019
4	Last transaction	8/30/2019
5	Opening	8/29/2019
6	Closure	8/28/2019
7	Last transaction	8/5/2019
8	Last transaction	8/24/2019
9	Last transaction	8/24/2019

The measure should count distinct Client ID, filter Operation in {"Last Transacion"} and then filter all the dates from today until 3 months ago.

Thanks in advance,

IC

[v-shex-msft · Accepted solution]

HI @Anonymous ,

I'd like to suggest you to use date and today functions to looping records and get last three month distinct count based on current date:

Measure =
CALCULATE (
    COUNTROWS ( VALUES ( Table[Client ID] ) ),
    FILTER (
        ALLSELECTED ( Table ),
        [Operation] = "Last transaction"
            && [Date]
                >= DATE ( YEAR ( TODAY () ), MONTH ( TODAY () ) - 3, DAY ( TODAY () ) )
            && [Date] <= TODAY ()
    )
)

Regards,

Xiaoxin Sheng

[Ashish_Mathur]

Hi,

Create a Calendar Table and build a relationship from the Date column of the Data Table to the Date column of the Calendar Table.  Try this measure

Measure = COUNTROWS(FILTER(SUMMARIZE(CALCULATETABLE(Values(Data[Client ID]),Data[Operation]="Last Transaction"),[Client ID],"ABCD",CALCULATE(DISTINCTCOUNT(Data[Client ID]),DATESBETWEEN(Calendar[Date],EDATE(TODAY(),-2),TODAY())),[ABCD]>0))

Hope this helps.