DAX Measure Latest Timestamp Filtering multiple columns- Help!

[Mruiz1868]

Hi there I have two report slicers that I created called REPORT PERIOD and DEPT NAME and I have a table called SP Narratives that I am trying to have both filters apply to it. In case anyone asks Yes I have all the proper relationships created in the backend associated with the proper cardinalities to the master tables. 

Business Case:

The dataset that I've connected to has muiltiple entries on a unique identifer (DeptID) which has a many to one relationship to the Department Name.  I intend to post only the record with the latest time stamp for each Dept. Name however with the dependencies on the Report month field. 

I've created two Succesful measures: 

Latest Time Stamp for each DepartName 

IS LAST = CALCULATE(MAX('SP_Narratives'[Completion time]), filter (all( 'SP_Narratives'),[DeptName] = EARLIER(SP_Narratives[DeptName])) )

Report Filter (Set to 1)

Lastdate filter = if(SP_Narratives[Completion time] = SP_Narratives[IS LAST], "1" , "0")

The two measures work fine with the Report Slicer "DEPART NAME" but lacks the consideration of Report Period (Report Month Field) which fails when I attempt to Filter by "REPORT PERIOD"

I've created a new measure that takes the Report Period into consideration but lacks the Dept.Name criteria:

IsNext = CALCULATE(MAX('SP_Narratives'[Completion time]), filter (all( 'SP_Narratives'),[ReportMonth] = EARLIER(SP_Narratives[ReportMonth])) )

I've researched a few articles on filters using Dax formulas but I can't seem to get the syntax correct! Ultimately I would like to have the Entries by latest time stamp that rolls up to the department name which you can filter by the Report month.

Any suggestions to the formula below is much appreciated!

Thanks!

IS LAST = CALCULATE(MAX('SP_Narratives'[Completion time]),

filter (all( 'SP_Narratives'),[DeptName] = EARLIER(SP_Narratives[DeptName]

&& 

filter (all( 'SP_Narratives'),[ReportMonth] = EARLIER(SP_Narratives[ReportMonth])) )

)) )

[v-yingjl]

Hi @Mruiz1868 ,

Based on your description, not certain what is your expected output. The formula may like this:

IS LAST =
CALCULATE (
    MAX ( 'SP_Narratives'[Completion time] ),
    FILTER (
        ALL ( 'SP_Narratives' ),
        [DeptName] = EARLIER ( SP_Narratives[DeptName] )
            && [ReportMonth] = EARLIER ( SP_Narratives[ReportMonth] )
    )
)

In addition, I have noticed that you quoted EARLIER() function in the formula so it could be a calculated column not a measure.

Basically needs more information about this issue like sample data or expected output etc. for further discussion. 

Sample data and expected output would help tremendously.
Please see this post regarding How to Get Your Question Answered Quickly:
https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490

Best Regards,
Community Support Team _ Yingjie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.