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

Enhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.

Reply
Hennemann
Frequent Visitor

Max Date for each month and value

Good morning community,

I have issues creating a new calculated column and couldn’t find a matching topic in the forum. Therefore I created a new one.

I would like to create a new column which identifies the last scantime for each month and value in my table. My source table looks something like this:

Sourcetable.png


The table filtered with the new column should look like this:

 

Finaltable.png

 

 

It is kinda tricky for me, because some values are duplicates, nevertheless they are still neccessary for me. There are also some months with no values at all.

 

I hope you can help me with my issue.

Thank you in advance and kinda regards

1 ACCEPTED SOLUTION
Fowmy
Super User
Super User

@Hennemann 

Add this as a column

Fowmy_0-1597648903669.png

 

 

Lastest =
IF (
    COUNTROWS (
        FILTER (
            GROUPBY (
                ADDCOLUMNS ( SCANS, "Month", EOMONTH ( SCANS[Lat Scantime], 0 ) ),
                SCANS[Job],
                [Month],
                "Date", MAXX ( CURRENTGROUP (), SCANS[Lat Scantime] )
            ),
            SCANS[Job] = [Job]
                && [Date] = [Lat Scantime]
        )
    ) > 0,
    [Lat Scantime],
    BLANK ()
)

 

 

________________________

Did I answer your question? Mark this post as a solution, this will help others!.

Click on the Thumbs-Up icon if you like this reply 🙂

YouTube, LinkedIn

Did I answer your question? Mark my post as a solution! and hit thumbs up


Subscribe and learn Power BI from these videos

Website LinkedIn PBI User Group

View solution in original post

4 REPLIES 4
Fowmy
Super User
Super User

@Hennemann 

Add this as a column

Fowmy_0-1597648903669.png

 

 

Lastest =
IF (
    COUNTROWS (
        FILTER (
            GROUPBY (
                ADDCOLUMNS ( SCANS, "Month", EOMONTH ( SCANS[Lat Scantime], 0 ) ),
                SCANS[Job],
                [Month],
                "Date", MAXX ( CURRENTGROUP (), SCANS[Lat Scantime] )
            ),
            SCANS[Job] = [Job]
                && [Date] = [Lat Scantime]
        )
    ) > 0,
    [Lat Scantime],
    BLANK ()
)

 

 

________________________

Did I answer your question? Mark this post as a solution, this will help others!.

Click on the Thumbs-Up icon if you like this reply 🙂

YouTube, LinkedIn

Did I answer your question? Mark my post as a solution! and hit thumbs up


Subscribe and learn Power BI from these videos

Website LinkedIn PBI User Group

harshnathani
Community Champion
Community Champion

Hi @Hennemann ,

 

You can use the following Column

 

Column = CALCULATE(MAX('Table'[LASTSCANTime]),ALLEXCEPT('Table','Table'[Job]))
 
Regards,
Harsh Nathani

Hello @harshnathani,

thank you for your feedback. Unfortunately this is not the solution for my problem.

 

I need the maxdate for each month and each jobname in combination.

 

Your suggested column will only show the max date for each jobname.

 

Kind regards

 

Hi @Hennemann ,

 

One way you can do is create 2 columns in your Table.

 

Month = MONTH('Table'[Date])
Year = YEAR('Table'[Date])
 
and then create a 
 
Table 2 =
SUMMARIZE('Table','Table'[Month],'Table'[Year],'Table'[Regions], "MAXX", CALCULATE(MAX('Table'[Date]),ALLEXCEPT('Table','Table'[Regions],'Table'[Month],'Table'[Year])))
 
Regards,
Harsh Nathani
Did I answer your question? Mark my post as a solution! Appreciate with a Kudos!! (Click the Thumbs Up Button)

Helpful resources

Announcements
July 2025 community update carousel

Fabric Community Update - July 2025

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

July PBI25 Carousel

Power BI Monthly Update - July 2025

Check out the July 2025 Power BI update to learn about new features.