Retrieving values and showing it on the current month

[PauloRicardo]

Hi, 

So, my company data base has a table called "historical_cost_center", it's main objective is to register where the employee worked in a given date.

COST_CENTER	ID	WORKER	ALTERATION_DATE
498000	10010	ADRIAN	04/20/2024
420105	10010	ADRIAN	06/01/2024
420047	10010	ADRIAN	09/01/2024
401022	43930	STEINHAUSER	06/24/2024
110014	19241	ZIELLO	05/23/2024
420078	19241	ZIELLO	07/01/2024

  Looking at Adrian example, he worked in three different cost_center throught the year.
  If my user filters for Adrian but want to see which cost center he is currently working (meaning, the user will want to see Today's date), it will return a BLANK, because the alteration date is for 09/2024, and we are currently in 10/2024.

Both filters in the images below are made through Calendar[Year] and Calendar[Month Name]:
The user filters by the same month (September) as that the alteration_date:

Now the user filters by a month (October) different from the alteration_date:

The goal here is to retrieve the current cost_center the employee is working on. Meaning that the result should be something like:

MONTH	CURRENT	DESIRED
April	498000	498000
May	BLANK	498000
June	420105	420105
July	BLANK	420105
August	BLANK	420105
September	420047	420047
October	BLANK	420047

[DanielW_]

Hey @PauloRicardo ,
With the following measure I was able to replicate the scenario. In the measure, first the latest alteration date is calculated for each worker, and then in a second step, return the coster center for that latest date value.

CurrentCostCenter =

VAR LatestDate =

    CALCULATE(

        MAX('historical_cost_center'[ALTERATION_DATE]),

        FILTER(

            'historical_cost_center',

            'historical_cost_center'[WORKER] = SELECTEDVALUE('historical_cost_center'[WORKER])

                && 'historical_cost_center'[ALTERATION_DATE] <= MAX('Calendar'[Date])

        )

    )

RETURN

    CALCULATE(

        MAX('historical_cost_center'[COST_CENTER]),

        'historical_cost_center'[ALTERATION_DATE] = LatestDate,

        'historical_cost_center'[WORKER] = SELECTEDVALUE('historical_cost_center'[WORKER])

    )

This was the result generated by this measure

[PauloRicardo]

Hi, @DanielW_ ,

Sorry for not answering earlier. I've tried your measure but yet it didn't returned the expected value.

I've already talked to the IT team and they decided to create a new table with the current cost_center month for month. Anyway, i appreciate your attention in helping me.

[DanielW_ · Accepted solution]

Glad to hear your colleagues are able to provide an alternative solution @PauloRicardo!
Out of curiosity I had another look and wasn't able to replicate my solution from before, however with an adjusted measure it did return the right values. This removes the filters from the Calendar table and then uses the latest date from Calendar (either from selection or the max date in the table) to filter the latest ALTERATION_DATE. This is then used to for retrieving the corresponding cost center.

CurrentCostCenter3 =

VAR LatestDate =

    CALCULATE(

        MAX(historical_cost_center[ALTERATION_DATE]),

        historical_cost_center[ALTERATION_DATE] <= MAX('Calendar'[Date]),

        ALL('Calendar')

    )

RETURN

    CALCULATE(

        MAX('historical_cost_center'[COST_CENTER]),

        'historical_cost_center'[ALTERATION_DATE] = LatestDate,

        ALL('Calendar')

    )

This is the outcome it produces:

and this is the model created for it:

It could probably be made more efficient, but just wanted to make the measure that does the job.

[PauloRicardo]

Hi @DanielW_ , good to hear from you again!

And it worked! The "ALL" function really did a difference. 

Thanks for the support! It will help many others with the same issue.

[Selva-Salimi]

Hi @PauloRicardo 

you can write a column to find out the finished date as follows:

Finished_date = if(isblank(calculate (min('Table'[Alteration]) , filter ('Table' , 'Table'[Worker]=EARLIER('Table'[Worker]) && 'Table'[Alteration]> EARLIER('Table'[Alteration])))) , today() , calculate (min('Table'[Alteration]) , filter ('Table' , 'Table'[Worker]=EARLIER('Table'[Worker]) && 'Table'[Alteration]> EARLIER('Table'[Alteration]))))

then write a measure to shoe in the card for latest cost center:

calculate (max(cost_center) , filter (all(table) , worker= selectedvalue(worker) && alteration_date >= selectedvalue (calendar[date]) && finished_date <= selectedvalue (calendar[date])))

If this post helps, then I would appreciate a thumbs up  and mark it as the solution to help the other members find it more quickly.

[PauloRicardo]

Hi, @Selva-Salimi ,

Sadly the values still come out as blank.

This is the result of the calculated column:

Here are the results for the measure presented:

Is there anything i can do to help?