First date of the month per client

[aryszpic]

Hello everyone,

I would like to ask for some help with something I couldn't manage to do or find in this forum, even if it looks like a very simple thing.

I have a table with [Client], [Date] and [$Bought]. What I want is to create a table that only contains the rows of the first [Date] of each month that the [Client] appear (which means the client bought something).

The following image represents the original table and the one I want to create:

https://imgur.com/4Me0krC

(The table is pretty big so I can't just remove specific rows in the Query Editor)

I couldn't manage to do this either as a calculated table or as a filter in the Query Editor...

Thanks in advance!

[aryszpic]

Hello everyone,

I would like to ask for some help with something I couldn't manage to do or find in this forum, even if it looks like a very simple thing.

I have a table with [Client], [Date] and [$Bought]. What I want is to create a table that only contains the rows of the first [Date] of each month that the [Client] appear (which means the client bought something).

The following image represents the original table and the one I want to create:

https://imgur.com/4Me0krC

I couldn't manage to do this either as a calculated table or as a filter in the Query Editor...

Thanks in advance!

[v-frfei-msft · Accepted solution]

Hi @aryszpic,

We can create a calculated column based on your original table.

Column =
IF (
    'Table'[Date]
        = CALCULATE (
            MIN ( 'Table'[Date] ),
            FILTER (
                ALLEXCEPT ( 'Table', 'Table'[Client] ),
                'Table'[Month] = EARLIER ( 'Table'[Month] )
            )
        ),
    1,
    0
)

Then create a new calculated table based on the original one.

Table1 = CALCULATETABLE('Table',FILTER('Table','Table'[Column]=1))

For more details, please check the pbix as attached.

Regards,

Frank

[aryszpic]

Worked wonderfully, thanks!

[Greg_Deckler]

Measure = MIN([Date]) 

If you want an actual calculated table and not just the above in a table visualization along with client and month, then:

Table = SUMMARIZE(Table,[Client],[Month],"_MinDate",MIN([Date]))

That last one might need tweaked or you might have to do it with a GROUPBY, I didn't test it.