Getting the value for a particular day from a period

[AliceW]

Hi ladies and gents,

I have an issue that maybe someone can help me with. Please bear with me.

I have one date table: 'Calendar Date', with dates from 01-Jan-2021 to 31-Dec-2022.

I have a second table, 'Cases', where I have the changes that occured:

Case ID	Field Change	Change Date	From	To
111	Status	01-Mar-2022	Green	Orange
111	Status	01-Apr-2022	Orange	Red
222	Status	01-Feb-2022	Orange	Red

In here, we can see the Case 111 was Green before March, was Orange in March, then Red in April and it is still Red.

What I need: the number of cases in a given color on any given date.

Ideally, I'll have a visual with the Calendar Date on the X Axis, and you can go see the number on

[AliceW]

Thank you very much for your time and effort on this!

[tamerj1 · Accepted solution]

Hi @AliceW 
This is a bet complex but it should work https://www.dropbox.com/t/JiyBXJ15DSSVZl2Z

A seperate table of all available "colors" shall be created

The data model has no relationships.

This is the code of the count measure

Count = 
SUMX (
    VALUES ('Date'[Date] ),
    CALCULATE (
        VAR CurrentFilterDate = MAX ( 'Date'[Date] )
        VAR SelectedColors = ALLSELECTED ( Colors[Color] )
        RETURN
            SUMX (
                Changes,
                VAR CurrentDate = Changes[Change Date]
                VAR CurrentFrom = IF ( Changes[From] IN SelectedColors, 1 )
                VAR CurrentTo = IF ( Changes[To] IN SelectedColors, 1 )
                VAR CurrentIDTable = CALCULATETABLE ( Changes, ALLEXCEPT ( Changes, Changes[Case ID] ) )
                VAR TableBefore = FILTER ( CurrentIDTable, Changes[Change Date] < CurrentDate )
                VAR TableAfter = FILTER ( CurrentIDTable, Changes[Change Date] > CurrentDate )
                VAR PreviousDate = MAXX ( TableBefore, Changes[Change Date] )
                VAR NexDate = MINX ( TableAfter, Changes[Change Date] )
                VAR PreviousTo = IF ( MAXX ( FILTER ( CurrentIDTable, Changes[Change Date] = PreviousDate ), Changes[To] ) IN SelectedColors, 1 )
                VAR NextFrom = IF ( MAXX ( FILTER ( CurrentIDTable, Changes[Change Date] = NexDate ), Changes[To] ) IN SelectedColors, 1 )
                RETURN
                    IF ( ISEMPTY ( TableBefore ) && Changes[Change Date] > CurrentFilterDate, CurrentFrom )
                        + IF ( ISEMPTY ( TableAfter ) && Changes[Change Date] <= CurrentFilterDate, CurrentTo )
                        + IF ( NOT ISEMPTY ( TableAfter ) && Changes[Change Date] <= CurrentFilterDate && Changes[Change Date] <= NexDate, CurrentTo )
            )
    )
)