Cumulative count of Entries and Exits

[Anonymous]

Hi,

I'm a bit new to Power BI and the community. I will try to be as thorough as possible, but let me know if I need to provide more information.

I have a table (Table1) with one row per sales item. In this table I have two date columns, one for Entry and one for Exit, plus a 'Status' column indicating if the item is sold or not. I then also have a Calendar-table where I have created relationships from 'Calendar'[Date] <- 'Table1'[EntryDate] (this is the active relationship), and from 'Calendar'[Date] <- 'Table1'[ExitDate] (the inactive relationship).

My goal is to create a 'Line and stacked column chart' showing cumulative number of Entries and Exits in current month, having the 'Calendar'[Date] column as the shared X axis. I'm succeeding in creating the cumulative line for Entries, see picture below. My code for cumulative entries looks like this: 

Entries_Cum = 
CALCULATE (
    COUNTROWS ( 'Table1' );
    FILTER(ALLSELECTED('Table1'); 'Table1'[EntryDATE]  <= MAX ('Table1'[EntryDATE]) );

 

But when calling the inactive date relationship (Exits), I fail to create a new line in my chart. My failing code for Cumulative Exits (which should count 20 items by day 20), looks like this: 

Exits_Cum = 
CALCULATE (
    COUNTROWS ( 'Table1' );
    FILTER(ALLSELECTED('Table1'); 'Table1'[ExitDATE]  <= MAX ('Table1'[ExitDATE]) &&
    'Table1'[Status] = "Sold" );
    USERELATIONSHIP(
        'Table1'[ExitDATE]; 'Calendar'[Date] ))

Anyone have an idea what I am doing wrong? In the chart I am using a Relative Date Filtering on the Date variable from the Calendar ('is in this month').

Finally, I am hoping to have the total Stock in the bars, which is basically the difference between the total number of rows subtracted by the numbers of rows that have Status = "Sold" and an ExitDate. This is more like a step 2 as of now.

Suggestions are appreciated! 

Per

[MFelix]

Hi @Anonymous,

When you are using a relationship between tables you need to use those relationship to filter out your result in this case the Calendar table is the one that defines your times period so on your formulas you need to have that as a input, on your second measure although you are activating the relationship the measure doesn't use the Calendar for any calculations so the result is null.

You need to make your table to filter out the calendar table and not the columns on your table 1 try to change your measures to (bold part is the change I made):

Entries_Cum = 
CALCULATE (
    COUNTROWS ( 'Table1' );
    FILTER(ALLSELECTED('Table1'); 'Table1'[EntryDATE]  <= MAX ('Calendar'[Date]) );

Exits_Cum = 
CALCULATE (
    COUNTROWS ( 'Table1' );
    FILTER(ALLSELECTED('Table1'); 'Table1'[ExitDATE]  <= MAX ('Calendar'[Date]) &&
    'Table1'[Status] = "Sold" );
    USERELATIONSHIP(
        'Table1'[ExitDATE]; 'Calendar'[Date] ))

Be aware that in the x-axis of the visual you should use the calendar table column and not any column from the Table1.

Regards,

MFelix

[Anonymous]

Hi @MFelix,

I think I follow what you mean. Although I tried your suggestion but didn't get it to work. Just to illustrate, in the table below, I have all the days in March '18.

I count the # of entries by:

Entries = COUNTROWS('Table1')

And then the # of exits as:

Exits = 
    CALCULATE(
        [Entries];
        FILTER(ALLSELECTED('Table1'); 'Table1'[Status] = "Sold");
        USERELATIONSHIP ( 'Table1'[ExitDATE]; 'Calendar'[Date] ) )

And then do the cumulative counts as you suggested. As you can see it works for Entries, but Exits and Exits_cum remains blank. Worth mentioning is that when I remove the Status="Sold" filter in the Exits count, the Exit column is populated (but need the filter to obtain correct count).

Best

[GeorgeBuster]

Hi Peranorm,

The behaviour of ALL functions sometimes is more complicated as they seem. You could try putting the boolean expression ('Table1'[Status] = "Sold") out of the FILTER, just in the CALCULATE. Another test would be cleaning completely the FILTER and using the ALLSELECTED as a filter table.

Best,

Jorge Bustillo.

[Anonymous]

 Hi @GeorgeBuster,

Thank you. Your suggestion did the trick for Exits! For the cumulative Exits, I am stilling having some issues. After putting the boolean expression ('Table1'[Status] = "Sold") out of the FILTER, this code now looks like this:

Exits_Cum = 
CALCULATE (
    COUNTROWS ( 'Table1' );
    'Table1'[Status] = "Sold";
    FILTER(ALLSELECTED('Table1'); 'Table1'[ExitDATE]  <= MAX ('Calendar'[Date]); 
    USERELATIONSHIP(
        'Table1'[ExitDATE]; 'Calendar'[Date] ))

I suspect there might be some problem with the filtering still, but I am not able to see it?

Thanks for helping out, @GeorgeBuster / @MFelix

Best,

Per

[GeorgeBuster]

Hi Peranorm,

What kind of issues? Could you share with us some screenshot? Thanks.

Best,

Jorge Bustillo.

[Anonymous]

Hi GeorgeBuster,

The problem is with the cumulative count of Exits, it only returns blank values. While the day-to-day non-cumulative count of Exits give me the results I expect. 

 

Thanks.

Best,

Per

[GeorgeBuster]

Hi Peranorm,

I have no clue what is happenig, the measure should work. Please share the 4 measures and I'll try to do my best.