Forum Discussion
Invoice Number Check
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 Anonymous @v-easonf-msft @speedramps
Hi Anonymous ,
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! 😀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 isPlease help me on this bro.
8 Replies
Hi Anonymous ,
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! 😀Anonymous
Please find the file here:
_______________
If I helped, please accept the solution and give kudos! 😀