Struggling to create Measure :( Please help!!

[ManuApo]

Hi community, 

I am really struggling to create a measure that basically shows the StatusID every day in a date calendar until it changes. My dataset has some IDs with different StatusID that changes along the time. The idea is to create a gantt chart where i can track along a month how often each ID changes its status. 

I created a dimension calendar table to have all the dates, as my fact table doesn't have all the dates.
Each row in my Fact table will have ID, StatusID and Date.  When an ID changes the status it will create a new row with a different StatusID an a new Date. 

I attach a picture of how it looks right now: 

- I have a date slicer on top of this matrix filtering the last 7 days.
- The matrix has ID as rows, Date from the dimension Calendar table as columns and StatusID as values.

- My objective is to create a measure that shows the max StatusID for each day of the dimension Calendar table

For example, the first rows of the table below will show 1s until the 25th of October where the StatusID will change to 2 and it will show a 2 until the 31st of October.

I hope I explain well my self, anyway I can clarify it. 

Thanks so much
M

[ManuApo]

Hi   @Greg_Deckler  thanks for your reply and your time. 

Unfortunately it didn't work. 




I want my measure to fill up every cell in the matrix with the current status for every calendar date until it changes. The end point will be to apply a background color to each status ID so I can track the changes easily.

Any ideas? 

FYI - I have removed the ALL function from the formula because I am appliying a filter on the page. 

[ManuApo]

Hi @Greg_Deckler  I am not sure if you saw my last reply. Hope you can help me with this. 

Thanks in advance!!

[v-easonf-msft]

Hi, @ManuApo 

Are you referring to using the state ID to fill all nulls of the matrix?

Not fully sure about your needs, please share a sample file for further research.

Best Regards,
Community Support Team _ Eason

[ManuApo]

Hello @v-easonf-msft 

Thanks for your reply! Yes that is what I am after. I want to fill all null values with the corresponding state ID on each date. 

[Greg_Deckler]

@ManuApo Try this:

Measure = 
  VAR __Date = MAX('Calendar'[Date])
  VAR __ID = MAX('Facts'[ID])
  VAR __Last = MAXX(FILTER(ALL('Facts'),[ID] = __ID && [Date]<= __Date),[Date])
  VAR __Result = MAXX(FILTER(ALL('Facts'),[ID] = __ID && [Date] = __Last),[Status])
RETURN
  __Result