cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Anonymous
Not applicable

Cumulative count of Entries and Exits

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]) );

 Entries Exits Stock.JPG

 

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

 

7 REPLIES 7
Super User
Super User

Re: Cumulative count of Entries and Exits

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



Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!




Anonymous
Not applicable

Re: Cumulative count of Entries and Exits

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.

Entries Exits table.JPG

 

 

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

Re: Cumulative count of Entries and Exits

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
Not applicable

Re: Cumulative count of Entries and Exits

 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

Re: Cumulative count of Entries and Exits

Hi Peranorm,

 

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

 

Best,

Jorge Bustillo.

Anonymous
Not applicable

Re: Cumulative count of Entries and Exits

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. 

 

Entries Exits table.JPG 

 

Thanks.

 

Best,

Per

Re: Cumulative count of Entries and Exits

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.

Helpful resources

Announcements
Community News & Announcements

Community News & Announcements

Get your latest community news and announcements.

Summit North America

Power Platform Summit North America

Register by September 5 to save $200

Virtual Launch Event

Microsoft Business Applications Virtual Launch Event

Watch the event on demand for an in-depth look at the new innovations across Dynamics 365 and the Microsoft Power Platform.

MBAS Gallery

Watch Sessions On Demand!

Continue your learning in our online communities.

Users Online
Currently online: 136 members 1,626 guests
Please welcome our newest community members: