Cumulative count with Blanks

[labuser1235]

Hi All,

I want the cumulative values where I was want to include blanks in the count. I had tried && where it counts blanks as well yet it doesnt work. As per the countrows it should start from 8, however it is starting from 6 as there is no date value for in date sold. 

I want it to include the blanks in the count. 

I have two tables Table1 and Date table.

Reverse cumulative wil help me to understand pending till date.

Can someone please help with with this.

Input: Table1

[Icey · Accepted solution]

Hi @labuser1235 ,

Try this:

Count =
CALCULATE (
    COUNTROWS ( Table1 ),
    FILTER ( ALLSELECTED ( Table1 ), Table1[Date Sold] >= MIN ( DateTable[Date] ) )
)
    + CALCULATE ( COUNTBLANK ( Table1[Date Sold] ), ALL ( Table1 ) )

Best Regards,

Icey

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

[Icey · Accepted solution]

Hi @labuser1235 ,

Try this:

Count =
CALCULATE (
    COUNTROWS ( Table1 ),
    FILTER ( ALLSELECTED ( Table1 ), Table1[Date Sold] >= MIN ( DateTable[Date] ) )
)
    + CALCULATE ( COUNTBLANK ( Table1[Date Sold] ), ALL ( Table1 ) )

Best Regards,

Icey

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

[labuser1235 · Accepted solution]

Thank you so much @Icey . I have tried another way too which worked as well. 

Count =
CALCULATE (
    COUNTROWS ( Table1 ),
    FILTER ( ALLSELECTED ( Table1 ), Table1[Date Sold] >= MIN ( DateTable[Date] ) || ISBLANK(TABLE1[DATE Sold]) )
)

[hstgeorge]

Also you could do the DistincCount function on the Table(Name) field and that should give you 8 and then put the date on the graph. 

[hstgeorge]

 Would it be possible for you to use and calculated column to replace the blanks fields with something else to indicate "NO DATE" and "INCOMPLETE".  That would populate the fields with a value and you wouldn't have to deal wtih the blanks anymore. Another option would be the ISBLANK(Value) which checks whether it true/false for blank and then you can build that into the functions below.  A couple different options.  If you think those would work, I can send over some potential calcs for you.

[labuser1235]

@hstgeorge Hi Thanks for you response, I cannot replace the columns rather I would want to use measure to count the values. If possible I have attached the PBI link

Please take a look if you count based on blanks in the measure. 

[amitchandak]

@labuser1235 ,Can you share sample data and sample output in table format? need to try on power Bi

[labuser1235]

Sure @amitchandak . 

Here is the PBI link

Thanks.

[amitchandak]

@labuser1235 , see if this file can help

[labuser1235]

@amitchandak Hi Amit, Thanks for your repsonse. It still shows cummulative however, i want reverse cummulative with blanks in the count too.