Return a value for a specific date when that value must be taken from a row with an earlier date

[Anonymous]

Hello all

Warning - DAX Newbie, and first-time poster to this forum. 

My gripe: I do have a table of projects where stage changes are recorded with a date stamp. What I would like to be able to ist to count all projects by stage for a given date, even though for that date there is no record stored.

Example: Project entered Stage B on 15 March 17, and changed to Stage A on 25 June 19. On the month-end report for March, April and May, project should be counted under Stage B. As of June month-end, under A.  Before getting to the counting bit, I already fail to extract the right stage for any given date. I can get the respective date where the last stage change occured, but cannot use that date as filter to give me the stage name (string).

The original table looks like this (table name is CIP_StageHistory):

Projects Stage Table

Furthermore, I have added a calendar table (Calendar), which I did not link to the project table on purpose., i.e. did not set up relationship. That way, I get all the calendar days irrespective of having records on that date or not.

The relevant stage change date I get through this measure:

Last Stage Date := CALCULATE (
MAX(CIP_StageHistory[Date]),
FILTER ( CIP_StageHistory, CIP_StageHistory[Date] <= MAX('Calendar'[Date]) )
)

The corresponding StageName I try getting through this measure, but this does not work, as the (formatted Excel) Pivot output shows below. It always gives me Stage B, irrespective of how I try to tweak the measure (min/max/lastnonblank etc.):

Last Stage := CALCULATE(MAX(CIP_StageHistory[StageName]),
FILTER(CIP_StageHistory,CIP_StageHistory[Date] = [LastDate]) )

This is the result - showing LastDate and Stage:

Any help would be greatly appreciated. 

If you could just point me to how to get that stage out - then I am on to the next challenge (counting them). Thank you in advance!

[Ashish_Mathur]

Hi,

I have solved a similar problem here - Summarise data by most recent status.

Hope this helps.

[Anonymous]

Thank you Ashish for your pointer. Will look into your solution and revert to this post. Best, Ueli

[jdbuchanan71 · Accepted solution]

Hello @Anonymous ,

Messed with this a bit, think I got it to work the way you wanted.  I do have my date table joined to the 'CIP_StageHistory' table.
I loaded my sample .pbix file here: https://www.dropbox.com/s/8tuhpxtid5szwez/OpportunityStage.pbix?dl=0

Last Stage Date = 
VAR RangeEnd = LASTDATE ( Dates[Date] )
RETURN
CALCULATE (
    MAX ( CIP_StageHistory[Date] ),
    FILTER ( ALL ( CIP_StageHistory ), CIP_StageHistory[Date] <= RangeEnd )
)

Last Stage = 
VAR StageDate = [Last Stage Date]

RETURN 
CALCULATE (
    MAX( CIP_StageHistory[StageName] ),
    FILTER ( ALL ( CIP_StageHistory ) , CIP_StageHistory[Date] = StageDate )
)

[DmitryKo]

This solution is INCORRECTLY marked as solution. The metrics do not provide desired results, and it could be easily verified over any sample dataset.

My guess is, it was marked as "accepted" automatically and/or by the person who proposed it due to no response from the question's author.

PLEASE REMOVE IT. It's misleading. The code is incorrect. It's not the solution.

P.S. It's really not hard to see that this code:

CALCULATE (
    MAX( CIP_StageHistory[StageName] ),
    FILTER ( ALL ( CIP_StageHistory ) , CIP_StageHistory[Date] = StageDate )
)

would return empty rows for the source table CIP_StageHistory when there are no rows corresponding to the date selected ("StageDate" variable in the code).

[jdbuchanan71]

@DmitryKo 

I don't mark my own posts as the solution and there is not auto marking done on the forum.  The original poster marked this as the solution.

If you read the original posters request they have gaps in their status where the want to show the newest status for months where there is no change and that is what my solution returns.

Example: Project entered Stage B on 15 March 17, and changed to Stage A on 25 June 19. On the month-end report for March, April and May, project should be counted under Stage B. As of June month-end, under A. 

Your issue is different in that you have multiple statuses in a day where the original poster had multiple days with the same status.

The fact that my post does not work on your problem does not make it wrong for the original question.

[DmitryKo]

It's the same question about determining some value per given unit of time - be it day or month, start or end of it. The code provided in "solution" here returns empty rows, as outlined above - where it shouldn't be.

And yes, there is some sort of auto marking here in effect; I had my own posts auto-marked by some system (not me) where solutions have been proposed (which are not actually solutions).

[jdbuchanan71]

In the sample I provided on this question the solution does not return any empty rows in any month so your structure must be different, I have not seen your example model.

[Anonymous]

Thank you for having taken the time to find a solution for me! Much appreciated. Will check with my data (full) data set and revert 🙂