Change in status

[unhnaht]

Hi, so I have this table representing change in status for Projects.

I want to demonstrate the status change of projects by a stacked bar chart, like this

Rule: if there are no changes in current month => status(current-month;company) = status(previous-month;company)

In other words, I want to calculate the latest status of a project from the BEGINNING OF TIME to the end of the calculated period

Expected output: 

A stacked bar chart that represents the change in status of projects (the number of projects remain the same - only the status components changes)

Problem: I'm using Power BI, but I only get these instead (the changes are only captured in the months they occured - not from the beginning of time - which means if there are no updates in Feb => the result returned for Feb would be 0; however, I want it to )

Is there anyway to write DAX functions to overcome this problem?

[Anonymous]

First, regarding the model... Why do you use bi-directional filters? I can't see any compelling need to do this. 99% of the time filtering should be one-way only, especially from dimensions to fact tables. The reasons behind this are too many to state them here (please refer to sources like "The Definitive Guide to DAX" by Alberto Ferrari and Marco Russo). Second, it looks to me that the statuses are increasing over time. Is this a hard rule or can they also decrease? Third, I'll try to write some formula soon.

[unhnaht]

Hi @Anonymous 

I have several diffferent status changes for 1 project in a month, but I only want to visualize the last status of the month/week.

Here is my data model

I have 2 updates for project I in june

the output is like this

Components of the chart:

I haven't found the way to visualize exactly what I planned to previously

[Anonymous]

// For any selection in dimensions
// (fact table always hidden!) this
// hidden measure will return the latest
// status visible in the Log fact table.
// If there are multiple statuses to
// choose from, it'll return the one
// with the highest value. If only one
// project is visible/selected, then
// it'll return is latest status.
// The "_" affix marks a helper, hidden measure.
[_LatestStatus] = 
MAXX(
    TOPN(1,
        CALCULATETABLE(
            'Log',
            // Bear in mind that 'Date Table' must 
            // be marked as the date table in the
            // model and Date must be the field that
            // stores consecutive dates without gaps.
            // I'm not sure but in your case the field
            // might be named Day. In any case, it has
            // to be of type Date.
            'Date Table' <= MAX( 'Date Table'[Date] )
            // This directive below is not required if
            // the table is indeed marked as the date
            // table in the model but it does no harm
            // to leave it in.
            REMOVEFILTERS( 'Date Table' ),
            REMOVEFILTERS( 'Status' )
        ),
        'Log'[Date],
        DESC
    ),
    'Log'[Status]
)

// Bear in mind that you need a separate dimension
// for statuses. You can't use any field from the
// fact table. This is always the case no matter what:
// fact tables must have all their columns hidden.
// The only exceptions to this rule are degenerate dimensions.
// Your Status dimension can be joined to the fact table
// on the [Status] field (one-way filtering).
// The measure below will return the number of projects
// with the latest status in the set of selected statuses
// (selection from the Status dimension, of course),
// which I believe you're after.
[# Projects] =
COUNTROWS(
    FILTER(
        Projects,
        [_LatestStatus] in DISTINCT( Status[Status] )
    )
)

// Please learn about the correct data modeling techniques:
// https://docs.microsoft.com/en-us/power-bi/guidance/star-schema

[unhnaht]

Hi @Anonymous 

I understand your point, haven't thought of that.

Still, I'm having some problems with duplicated values (several entry per month of 1 project will still be recorded => It doesn't really serve my purpose in showing the final status of each project in the month

The sample 

Could you help me out? Thank you.

[Anonymous]

Hi @unhnaht 

I don't quite get this. Do you have several different values/statuses for a project in a month? Or do you have several SAME values/statuses for a project in a month? If the latter is the case, then it's rather easy to show the one value despite duplicates. If the former is the case, you have to decide on the algorithm. Sorry, I can't see the sample due to my company's file-handling policy.

[Anonymous]

It's very simple to fix this. Just use Power Query to fill in the blanks for the months where there was no change in status with the status of the n-th previous month that you have a value for. In other words, some problems are not fixed with fancy DAX (which will be slow, by the way) but by changing the model itself. Just make the data consistent in every month for every project and be done with this in no time.

[amitchandak]

@unhnaht , The one you are showing looks like a 100% stacked bar chart. You have 2 visuals 100% stacked. The total in your case does seem to add up to 10. So 100% stacked is the way to go.

[unhnaht]

Thank you @amitchandak

However, I think I'm having another problem.

I want to demonstrate the change in components of the projects throughout the month.

For example: in Jan, there are 2 projects in status 1, 2 projects in status 2 etc. and in Feb, 2 (out of 10) projects changed it's statuses - the current situation is 2 project status 1,..., 1 in status 6 etc.

The number of project remains constant, only the statuses change. I want to show changes compared to the whole.

The problem I'm having is,

if there are no changes in project A in Feb => power BI (MAX function) will return blank value (it only calculates the last value from beginning of the month to the end of the month)

however, I want to calculate the last value from THE BEGINNING OF TIME (NOT from the beginning of month) to the end of the calculated month.

Which means, if A has no changes in Feb, it will take the status from Jan.

I don't know how to write measures to calculate that, hope you can help me.