Invoice Number Check

[Jeevan1991]

Dear Friends,

I have a below set of data.

Date	Outlet	Invoice #
01-Nov-2020	12435452	123
02-Nov-2020	12435452	124
03-Nov-2020	12435452	125
03-Nov-2020	12435452	127
01-Nov-2020	53435454	765
02-Nov-2020	53435454	766
03-Nov-2020	53435454	777
03-Nov-2020	53435454	780

I want to check the outletwise invoice number incremnt whether it is increased by 1 or not. Ultimately i want to find the result like the below one.

Date	Outlet	Invoice #	Invoice #
01-Nov-2020	12435452	123	First Invoice number
02-Nov-2020	12435452	124	Incresed By 1
03-Nov-2020	12435452	125	Incresed By 1
03-Nov-2020	12435452	127	Not Incresed By 1
01-Nov-2020	53435454	765	First Invoice number
02-Nov-2020	53435454	766	Incresed By 1
03-Nov-2020	53435454	777	Not Incresed By 1
03-Nov-2020	53435454	780	Not Incresed By 1

@amitchauhan @amitchandak @jazzk @v-easonf-msft @speedramps

[lkalawski · Accepted solution]

Hi @Jeevan1991 ,

A first thing - please remove a summarization for the Rank column:

Second thing - please add Dense to Rank function as a last argument.

Rank = 
RANKX (
    FILTER (
        DAILY_BILLWISE_SALES,
        DAILY_BILLWISE_SALES[CAFEID] = EARLIER ( DAILY_BILLWISE_SALES[CAFEID] )
    ),
    DAILY_BILLWISE_SALES[BILLNO],
    ,
    ASC,
    Dense
    
)

The result:

pbix file: https://gofile.io/d/phJrox 
_______________
If I helped, please accept the solution and give kudos! 😀

[lkalawski]

Hi @Jeevan1991 ,

You can use calculated column and measure to achieve this:
Calculated column:

Rank = 
RANKX (
    FILTER (
        'Table',
        'Table'[Outlet] = EARLIER ( 'Table'[Outlet] )
    ),
    'Table'[Invoice #],
    ,
    ASC
)

Measure:

Invoice Order = 
VAR __PreviousNumber = MAX('Table'[Rank]) - 1
VAR __Diff = 
MAX('Table'[Invoice #]) -
CALCULATE(
     MAX('Table'[Invoice #]), FILTER(ALL('Table'), 'Table'[Outlet] = MAX('Table'[Outlet]) && 'Table'[Rank] = __PreviousNumber)) 

RETURN 
 SWITCH(MAX('Table'[Rank]),
 1, "First Invoice number",
 SWITCH(__Diff,
 1, "Incresed By 1",
 "Not Incresed By 1"
 )
 )

The result:

_______________
If I helped, please accept the solution and give kudos! 😀

[Jeevan1991]

Hi @lkalawski 

Thanks for the solution.

Please send me PBIX file please.

[lkalawski]

@Jeevan1991 

Please find the file here:

https://gofile.io/d/f8x4KD

_______________
If I helped, please accept the solution and give kudos! 😀

[Jeevan1991]

Bro,

For me RANK is giving me different result.

For used is.

[lkalawski]

@Jeevan1991 

Do you have the same value for Rank in the Data View?

Could you please share your pbix file?

_______________
If I helped, please accept the solution and give kudos! 😀 

[Jeevan1991]

Hi Bro,

In the below link i have uploaded the sample file.

https://gofile.io/d/FKTse6 

[lkalawski · Accepted solution]

Hi @Jeevan1991 ,

A first thing - please remove a summarization for the Rank column:

Second thing - please add Dense to Rank function as a last argument.

Rank = 
RANKX (
    FILTER (
        DAILY_BILLWISE_SALES,
        DAILY_BILLWISE_SALES[CAFEID] = EARLIER ( DAILY_BILLWISE_SALES[CAFEID] )
    ),
    DAILY_BILLWISE_SALES[BILLNO],
    ,
    ASC,
    Dense
    
)

The result:

pbix file: https://gofile.io/d/phJrox 
_______________
If I helped, please accept the solution and give kudos! 😀

[Jeevan1991 · Accepted solution]

Hi Bro,

The above method giving me inconsistent results.

For an example look at the below screenshot where i have filtered just one cafe.

Even though "BillNo" 102 is the first bill number it is showinng the rank as 21, because of this invoice Order too showing wrongly.

Column Used is = 

Measure used is 

Please help me on this bro.