cancel
Showing results for
Did you mean:

Fabric is Generally Available. Browse Fabric Presentations. Work towards your Fabric certification with the Cloud Skills Challenge.

Resolver I

## How to get the max service date of data per month for a calculated column by each month

I am trying to get the max service date per month and load into a column.  I have this working in another workbook but it won't for some reason replicate.  My formula is

MaxPerMonth = Calculate(Max(Members_Claims[SERVICE_DT]),FILTER('2017dates','2017dates'[YearMonth] = EARLIER('2017dates'[YearMonth])))

Which is giving me the max of the data in the entire dataset which is 11/1.  I am trying to have a column like this:

First Date     MaxDataPerMonth

1/1/2017     1/31/2017

2/1/2017     2/28/2017

.....

11/1/2017   11/1/2017  Because we only have data as of 11/1 right now.

Any ideas?  Thanks

1 ACCEPTED SOLUTION
Resolver I

Thanks for responding.  I tried your formula but it did not work but it gave me a jumpstart and I ended up with

which gave me 1/31/2017, 2/28/2017, .......11/1/2017 which is what I needed but it left me with some blank rows in the DataDay column so used the Summarize function to create a new table that eliminated the blank rows.

3 REPLIES 3
Community Support

HI @shelbsassy,

You can refer to below steps to simply get first date and max date of current month.

Steps:

1. Drag date column to table visual, switch it to hierarchy mode and keep year month.
2. Add above column twice to table visual, switch them to normal date mode and modify summary mode to 'earliest' and 'latest'.

calendar Last Date is 11/9/2017, so last month max date is 11/9/2017.

Regards,

Xiaoxin Sheng

Community Support Team _ Xiaoxin
If this post helps, please consider accept as solution to help other members find it more quickly.
Solution Sage

Hi @shelbsassy,

I created this column with this formula where I have got my table Data and its field Date. Please give us a dummy sample if it did not solve your problem.

```Test =
IF (
MAX ( Data[Date] ) > Data[Date],
ENDOFMONTH ( Data[Date] ),
MAX ( Data[Date] )
)```

Thx

Ninter

Resolver I

Thanks for responding.  I tried your formula but it did not work but it gave me a jumpstart and I ended up with

which gave me 1/31/2017, 2/28/2017, .......11/1/2017 which is what I needed but it left me with some blank rows in the DataDay column so used the Summarize function to create a new table that eliminated the blank rows.

Announcements

#### Power BI Monthly Update - November 2023

Check out the November 2023 Power BI update to learn about new features.

#### Fabric Community News unified experience

Read the latest Fabric Community announcements, including updates on Power BI, Synapse, Data Factory and Data Activator.

#### The largest Power BI and Fabric virtual conference

130+ sessions, 130+ speakers, Product managers, MVPs, and experts. All about Power BI and Fabric. Attend online or watch the recordings.

Top Solution Authors
Top Kudoed Authors