Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago

Find status for a customer

Hi guys,   I am trying to build a report that can tell me whether a customer is new / current / lost. This depends on the when they last ordered something at the company. I thought I had it figur...
Need Help
v-eachen-msft's avatar
v-eachen-msft
Icon for Community Support rankCommunity Support
6 years ago

Hi Anonymous ,

 

You could unpivot these 4 columns as one column in the query editor. Then you could remove "Sales_" and only keep years.

Then you could use SUMMARIZE().

Table =
SUMMARIZE (
    Sheet1,
    Sheet1[Accountnumber],
    "Status", IF (
        CALCULATE (
            SUM ( Sheet1[Value] ),
            FILTER (
                Sheet1,
                Sheet1[Attribute]
                    > ( YEAR ( TODAY () ) - 3 )
                    && Sheet1[Attribute] < YEAR ( TODAY () )
            )
        ) > 0,
        "Current",
        IF (
            CALCULATE (
                SUM ( Sheet1[Value] ),
                FILTER ( Sheet1, Sheet1[Attribute] = YEAR ( TODAY () ) )
            ) > 0,
            "New",
            "Lost"
        )
    )
)