Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago
Solved

Simple measure

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/...
Need Help
  • Anonymous's avatar
    Anonymous
    7 years ago

    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's avatar
Ashish_Mathur
Icon for Super User rankSuper User
7 years ago

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.