Project model – Dynamic Status Over time

[MortenAlbrecht]

I have build a small project model, where each project status can either be open or closed depend upon which month we are filtering upon.

Basically I want a table, where I can see all Invoiced in time filter.
I also want to be able to filter on the project status, but it should the status at the end of the time period.
E.g. I want to see all projects which where open last month and see the total invoiced.
But I also want to see what has been invoiced month by month for all the project which were open last month. I want to that in a line chart of column chart.
I can make it work with a table and a matrix, but when I start to use charts then it stops working!!

What do I do?

I have attached the model, so you can see how it’s build.
https://integratordk-my.sharepoint.com/:u:/g/personal/morten_albrechtsen_integrator_dk/EfsnJbWIioxOi... 

Comments:

The Project status is a measure, because it’s dynamic and can change over time.
To still be able to use it as a filter/dimension I have create manual dimension. (Dim Project Status)
I have project status fact table, so we can see when the project is changing status. (in the real model it’s created as slowing changing dimension coming from the Dim Project)

To “link” the project status measure and the dimension, I have created at new measure called Project status filter, which I’m using on all table, matrix, charts.
…for some weird reason it doesn’t work on charts!! 

[Anonymous]

Hi, @MortenAlbrecht 

Are you able to pinpoint the output you are expecting and what the problem is based on the documentation you provide?

Best Regards

Jianpeng Li

[MortenAlbrecht]

First, of all, can you see the model I linked to?....so you can download it and see the example in detail

As you can see from the picture, i only got two project and no 2 closes in April.
That means if have have set the time filter from jan-apr AND also set the status filter to open, then I only want to see project no. 1...in both the table ( in the buttom), the matrix in the upper right hand side and ESPECIALLY the bar chart in upper left hand side.
Does it make sense?

[hackcrr]

Hi, @MortenAlbrecht 

Perhaps you could try the following Dax expression

ProjectStatusAtEnd = 
VAR MaxDate = MAX('Date'[Date])
RETURN
CALCULATE(
    LASTNONBLANKVALUE(
        'ProjectStatus'[Status],
        'ProjectStatus'[Date] <= MaxDate
    ),
    REMOVEFILTERS('Date')
)

FilteredProjects = 
CALCULATE(
    SUM('Project'[Invoiced]),
    FILTER(
        'Project',
        [ProjectStatusAtEnd] = "Open" -- Or any desired status
    )
)

Best Regards,

hackcrr

If this post helps, then please consider Accept it as the solution and kudos to this post to help the other members find it more quickly