Check month by month one field until criteria is match - then return a date.

[romovaro]

HI

Good morning. I might need a little bit of help with a formula.

I have a Matrix table with names (productity Owner) and date as column,

The table shows actual Projects vs monthly target and difference. 

What I would like to have is a formula that check for every productivity owner when is the Month that they can assume more projects.

Rule to determine next GLD slot= nearest month from current month on which GLD target is below target.

For example 1 has 3 extra projects in January but February has zero. Feb 2025 is the next GLD slot for him

For example 2 has 0 availabiltiy in January, +1 in Feb, + 1 in March, 0 in April and next GLD available is May 2025

The idea is to check month by month starting current month until the Diff column is BLANK or Negative

Any tip is welcome

Thanks,

[Wilson_]

Hi romovaro,

Try something like the below as a measure:

Next GLD Slot =
VAR Summarized =
ADDCOLUMNS (
    SUMMARIZECOLUMNS ( Table[Productivity Owner], Calendar[yyyy-mm] ),
    "Diff", [#Projects] - [#Target]
)
VAR Filtered =
FILTER (
    Summarized,
    [Diff] < 0 || ISBLANK ( [Diff] )
)
VAR Result =
MINX (
    Summarized,
    Calendar[yyyy-mm]
)

RETURN Result

I'm making assumptions about your data model since you haven't provided it.

• You have a date table with a year and month column (I called it Calendar[yyyy-mm] above)
• #Projects and #Target are measures

If this doesn't work for you and you aren't able to tweak it, please share your data model (or at least a sample) and I'd be happy to tweak the measure further. 😄

----------------------------------
If this post helps, please consider accepting it as the solution to help other members find it quickly. Also, don't forget to hit that thumbs up and subscribe! (Oh, uh, wrong platform?)

P.S. Need a more in-depth consultation for your Power BI data modeling or DAX issues? Feel free to hire me on Upwork or DM me directly on here! I would love to clear up your Power BI headaches.

[romovaro]

Thanks Wilson

I do get a date with your formula but it seems is not 100% accurate

1 should show 2025-03-01 but is showing 2025-04-01

2 should show 2025-05-01 but is hoswing 2025-03-01

checking....

• You have a date table with a year and month column (I called it Calendar[yyyy-mm] above)
  • I do have a calendar table but using the GLD column for this table. 
• #Projects and #Target are measures
  • Project is a calcualted column and Target in another table.
• 

Number of project is a combination of requisites. Same customer, Same Country and Same Go live Date Month

NumProjects = IF (

    OR ( OR (

        'PV Cel File'[CID]<>

    CALCULATE (

        MAX ('PV Cel File'[CID]),

        FILTER (

            'PV Cel File',

            'PV Cel File'[Índice] = EARLIER ('PV Cel File'[Índice])-1)

    ),

    'PV Cel File'[Country Long Name]<>

    CALCULATE (

        MAX ('PV Cel File'[Country Long Name]),

        FILTER (

            'PV Cel File',

            'PV Cel File'[Índice] = EARLIER ('PV Cel File'[Índice])-1)

    )

    ),

        'PV Cel File'[GLD]<>

        CALCULATE(

            MAX('PV Cel File'[GLD]),

            FILTER(

                'PV Cel File',

            'PV Cel File'[Índice] = EARLIER ('PV Cel File'[Índice])-1)

        )

    )

    ,1

    ,0

    )

[romovaro]

HI @Wilson_ 

tried to change your formula but still having issues.

IPM NextMonthCheck =

VAR CurrentMonth = MAX('PV Cel File'[GLD])

VAR Next  = CALCULATE(

    MIN('PV Cel File'[GLD]),

    'PV Cel File'[GLD] > CurrentMonth

)

RETURN

IF(

    ISBLANK([Diff to target]) || [Diff to target] < 0,

    Next,

    BLANK()

)

[bhanu_gautam]

@romovaro First, create a calculated column that checks if the difference (Diff) is blank or negative for each month.

DAX
Availability =
IF(
ISBLANK('Table'[Diff]) || 'Table'[Diff] < 0,
1,
0
)

Then create a measure that finds the nearest month from the current month where the availability is 1.

NextGLDSlot =
VAR CurrentMonth = MONTH(TODAY())
VAR CurrentYear = YEAR(TODAY())
VAR FilteredTable =
FILTER(
'Table',
'Table'[Productivity Owner] = EARLIER('Table'[Productivity Owner]) &&
'Table'[Availability] = 1 &&
(
'Table'[Year] > CurrentYear ||
('Table'[Year] = CurrentYear && 'Table'[Month] >= CurrentMonth)
)
)
VAR NextMonth =
MINX(
FilteredTable,
DATE('Table'[Year], 'Table'[Month], 1)
)
RETURN
NextMonth

Add the NextGLDSlot measure to your matrix table to see the next available month for each productivity owner

[romovaro]

hi @bhanu_gautam Bhanu_gautam

thanks. I created the calculated column but the measure that finds the nearest month from the current month where the availability is 1 is gettng an error...

NextGLDSlot =

VAR CurrentMonth = MONTH(TODAY())

VAR CurrentYear = YEAR(TODAY())

VAR FilteredTable =

FILTER(

'PV Cel File',

'PV Cel File'[Productivity Owner] = EARLIER ('PV Cel File'[Productivity Owner]) &&

'PV Cel File'[GLD Availability] = 1 &&

(

'PV Cel File'[Year] > CurrentYear ||

('PV Cel File'[Year]= CurrentYear && 'PV Cel File'[Month] >= CurrentMonth)

)

)

VAR NextMonth =

MINX(

FilteredTable,

DATE('PV Cel File'[Year], 'PV Cel File'[Month], 1)

)

RETURN

NextMonth