Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
aryszpic
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
v-frfei-msft
Community Support
Community Support

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

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
Community Support
Community Support

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.

Worked wonderfully, thanks!

Greg_Deckler
Super User
Super User

 

 

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. 


@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.