Cannot filter dates on matrix with measure

[fe_username]

Model :

Dates table goes from 1st January 2025 to Last Date of Step Date.

I have a matrix with rows Document ID, columns Dates[Date], and values this measure

Advancement weight without last fridays=
VAR CurrentDate = MAX(Dates[Date])
VAR DocumentID = MAX(discipline_whole[Document ID])
VAR WeightFromMilestone = [Weight From Milestone]
VAR MaxRelativeWeightage =
    CALCULATE (
        MAX(discipline_whole[Relative weightage]),
        discipline_whole[Document ID] = DocumentID
    )
RETURN

    WeightFromMilestone * MaxRelativeWeightage

and I get this output (correct for values but does not have the correct dates)

I took the measure above and modified it in order to filter the last friday of month

Ponderated Weightage Advancement = 
VAR CurrentDate = MAX(Dates[Date])
VAR DocumentID = MAX(discipline_whole[Document ID])
VAR WeightFromMilestone = [Weight From Milestone]
VAR MaxRelativeWeightage =
    CALCULATE (
        MAX(discipline_whole[Relative weightage]),
        FILTER(
            ALL(Dates),  -- Rimuove qualsiasi filtro dalla tabella Date
            max(discipline_whole[Document ID]) = DocumentID
        )
    )

-- Check if the current date is the last Friday of the month
VAR WeekdayOfCurrentDate = WEEKDAY(CurrentDate, 2)  -- 2 significa che la settimana inizia da lunedì (1 = lunedì, 7 = domenica)
VAR EndOfMonthDate = EOMONTH(CurrentDate, 0)  -- Ottieni l'ultimo giorno del mese
VAR LastFridayOfMonth = EndOfMonthDate - WEEKDAY(EndOfMonthDate, 2) + 5  -- Calcola l'ultimo venerdì del mese

-- Restituisci il risultato solo se la data corrente è l'ultimo venerdì del mese
RETURN
IF(WeekdayOfCurrentDate = 5 && CurrentDate = LastFridayOfMonth,
    WeightFromMilestone * MaxRelativeWeightage,
    BLANK())  -- Restituisce BLANK() se non è l'ultimo venerdì del mese

with following output (correct values but not the dates I'm looking for)

What I'm trying to obtain is like the first matrix, with dates starting From January and going to the last date of Step Date, these dates for each month must be last friday of month.
I tried to remove all filters on Date but I can't get more than this. How can I make it to filter the last friday of month, for each month in the matrix?
Thank you

[v-sathmakuri]

Hi @fe_username ,

I hope this information provided is helpful. Feel free to reach out if you have any further questions or would like to discuss this in more detail. If responses provided answers your question, please accept it as a solution so other community members with similar problems can find a solution faster.

Thank you!!

[v-sathmakuri]

Hi @fe_username ,

I wanted to check if you had the opportunity to review the information provided. Please feel free to contact us if you have any further questions. If the responses has addressed your query, please accept it as a solution and give a 'Kudos' so other members can easily find it.

Thank you.

[v-sathmakuri]

Hi @fe_username ,

Thank you for reaching out to Microsoft Fabric Community.

Thank you @bhanu_gautam  for prompt response.

May I ask if you have resolved this issue? If so, please mark the helpful reply and accept it as the solution. This will be helpful for other community members who have similar problems to solve it faster.

If this post helps, then please consider Accepting as solution to help the other members find it more quickly, don't forget to give a "Kudos" – I’d truly appreciate it! 

Thank you.

[bhanu_gautam · Accepted solution]

@fe_username , Try using

DAX
Ponderated Weightage Advancement =
VAR CurrentDate = MAX(Dates[Date])
VAR DocumentID = MAX(discipline_whole[Document ID])
VAR WeightFromMilestone = [Weight From Milestone]
VAR MaxRelativeWeightage =
CALCULATE (
MAX(discipline_whole[Relative weightage]),
FILTER(
ALL(discipline_whole), -- Ensure the filter is on the discipline_whole table
discipline_whole[Document ID] = DocumentID
)
)

-- Check if the current date is the last Friday of the month
VAR WeekdayOfCurrentDate = WEEKDAY(CurrentDate, 2) -- 2 means the week starts on Monday (1 = Monday, 7 = Sunday)
VAR EndOfMonthDate = EOMONTH(CurrentDate, 0) -- Get the last day of the month
VAR LastFridayOfMonth = EndOfMonthDate - WEEKDAY(EndOfMonthDate, 2) + 5 -- Calculate the last Friday of the month

-- Return the result only if the current date is the last Friday of the month
RETURN
IF(WeekdayOfCurrentDate = 5 && CurrentDate = LastFridayOfMonth,
WeightFromMilestone * MaxRelativeWeightage,
BLANK()) -- Return BLANK() if it is not the last Friday of the month