Two DAX calculated column needed for calculate returning customer series and no of invoice

[PijushRoy]

Hi Team,

I have data like below (from column 1 to 3) and I need to create two calculated column for 4 and column 5

PBIX file attached for sample data and two require column

Thanks in Advance

[MFelix]

Hi @PijushRoy ,

Try the following two codes:

Hown Many Invoices = 
VAR temptable =
    TOPN (
        1,
        FILTER (
            Data,
            Data[Unique_Cust] <> 1
                && Data[Index] > EARLIER ( Data[Index] )
        ),
        Data[Index], ASC
    )
VAR Row_Number =
    MINX ( temptable, Data[Index] ) - Data[Index]
VAR TotalRows =
    MAXX ( Data, Data[Index] )
RETURN
    IF (
        Data[Unique_Cust] <> 1,
        IF ( Row_Number > 0, Row_Number, TotalRows - Data[Index] + 1 )
    )

[PijushRoy]

Hi @MFelix 
Small mismatch
It is taking Index 4 rows Invoice number inplace of Index 25 Invoice no

[MFelix · Accepted solution]

Hi @PijushRoy ,

Redo the calculation to:

CUST SERIES = 
VAR MAXINVOICE =
    IF ( Data[Hown Many Invoices] = 1, Data[Index],Data[Index]+ Data[Hown Many Invoices]-1 )
RETURN
    IF (
        Data[Hown Many Invoices] <> BLANK (),
        Data[Invoice_ID] & "-"
            & MAXX ( FILTER ( ALL ( Data ), Data[Index] = MAXINVOICE ), Data[Invoice_ID] )
    )

[vanessafvg]

@PijushRoy  you haven't provided more information

i haven't done all of it but is this what you are looking for?  please confirm - you haven't give enough information, you havent provided an expected solultion.  If you need help you do need to provide a clear set of what is expected.

[PijushRoy]

@vanessafvg 
Can you please share the pbix, when I applied calcualtion, I am not getting same data as you mentioned in image.

[vanessafvg]

it changed thats probably why.  Its still not perfect  however but i am unsure of what you want so please clarify with the exact results.

Get UniqueCust =
VAR selinvoiceid =
SELECTEDVALUE ( Data[Invoice_ID] )
VAR mininvoiceid =
CALCULATE ( MIN ( Data[Invoice_ID] ) )
VAR maxindex =
CALCULATE (
MIN ( Data[Index] ) - 1,
ALL ( data ),
Data[Invoice_ID] > mininvoiceid
&& Data[Unique_Cust] > 1
)
VAR minindex =
CALCULATE (
MAX ( Data[Index] ),
ALL ( data ),
Data[Index] < maxindex
&& Data[Unique_Cust] > 1
)
VAR firstinvoiceid =
CALCULATE ( MIN ( Data[Invoice_ID] ), ALL ( Data ), Data[Index] = minindex )
VAR maxinvoiceid =
CALCULATE ( MAX ( Data[Invoice_ID] ), ALL ( data ), Data[Index] = maxindex )
VAR result =
IF ( selinvoiceid = maxinvoiceid, selinvoiceid, firstinvoiceid ) & "-"
& IF ( ISBLANK ( maxinvoiceid ), mininvoiceid, maxinvoiceid )
RETURN
result

[Ashish_Mathur]

Hi,

In a simple MS Excel file, please show the expected result with formulas/comments.

[vanessafvg]

its not perfect but its a something to work with i needs more tweaking

Get UniqueCust =
VAR mininvoiceid =
MIN ( Data[Invoice_ID] )
VAR maxindex =
IF (
SUM ( Data[Index] ) > 1,
CALCULATE (
MIN ( Data[Index] ) - 1,
ALL ( data ),
Data[Invoice_ID] > mininvoiceid
&& Data[Unique_Cust] > 1
),
BLANK ()
)
VAR maxinvoiceid =
CALCULATE ( MAX ( Data[Invoice_ID] ), ALL ( data ), Data[Index] = maxindex )
RETURN
mininvoiceid & "-"
& IF ( ISBLANK ( maxinvoiceid ), mininvoiceid, maxinvoiceid )

[PijushRoy]

@vanessafvg 
Thanks for your help, however it is not working and not shoiwng correct result.

[vanessafvg]

as i said it needs tweaking.  however agree providing more information might be useful as the in expected result for each row.

[PijushRoy]

Hi,

Any help please

[PijushRoy]

Thank you all for support 
@MFelix @vanessafvg @rsbin @Ashish_Mathur 

[rsbin]

@PijushRoy ,

Please refer to modified pbix attached.

Edit: 

Just realized I didn't follow through with all your requirements.  Hoping though you can see the pattern and make the necessary modifications.

Trust you will be able to follow the logic.

Any questions, please ask.

Best Regards,

[PijushRoy]

Hi @rsbin 

Thanks for your help, but it is not working
Series ending value is not matching
Invoice count number is not correct for all

[rsbin]

@PijushRoy ,

 I thought the logic of capturing the first 3 characters would work, but unfortunately

 this appears not to be the case.

Looks like another approach or method will be required.

Might be worthwhile closing this thread and start a new one.

Revised file attached.  Gets a little closer but still instances where logic is incomplete

[PijushRoy]

Hi @rsbin 

I need to solve this urgently, I am also trying. Can you please try and let me kwon, It will great help for me.