cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
aryszpic Frequent Visitor
Frequent Visitor

First date of the month per client

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!

1 ACCEPTED SOLUTION

Accepted Solutions
Community Support Team
Community Support Team

Re: First date of the month per client

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))

Capture.PNG

For more details, please check the pbix as attached.

 

Regards,

Frank

Community Support Team _ Frank
If this post helps, then please consider Accept it as the solution to help the others find it more quickly.

View solution in original post

4 REPLIES 4
aryszpic Frequent Visitor
Frequent Visitor

First date of Client-Month

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!

Super User IV
Super User IV

Re: First date of the month per client

 

 

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. 


I have book! Learn Power BI from Packt


Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!

Community Support Team
Community Support Team

Re: First date of the month per client

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))

Capture.PNG

For more details, please check the pbix as attached.

 

Regards,

Frank

Community Support Team _ Frank
If this post helps, then please consider Accept it as the solution to help the others find it more quickly.

View solution in original post

aryszpic Frequent Visitor
Frequent Visitor

Re: First date of the month per client

Worked wonderfully, thanks!

Helpful resources

Announcements
Exclusive LIVE Community Event #3 – Ask Arun Anything

Exclusive LIVE Community Event #3 – Ask Arun Anything

Join us in the third Triple A event!

Meet the 2020 Season 1 Power BI Super Users!

Meet the 2020 Season 1 Power BI Super Users!

It’s the start of a new Super User season! Learn all about the new Super Users and brand-new tiered recognition system.

Super User Challenge: Can You Solve These?

Super User Challenge: Can You Solve These?

We're celebrating the start of the New Super User season with our first ever Super User 'Can You Solve These?' challenge.

Power BI Desktop Update - February 2020

Power BI Desktop Update - February 2020

We are super excited for our update this month, as we are releasing two of our top community requests!

Power Platform Online Conference

Power Platform Online Conference

Join us for the first ever Power Platform Online Conference!

Top Solution Authors