Forum Discussion

PijushRoy's avatar
PijushRoy
Community Champion
3 years ago
Solved

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

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

Need Help
  • MFelix's avatar
    MFelix
    3 years ago

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

     

     

16 Replies

  • vanessafvg's avatar
    vanessafvg
    Community Champion
    3 years ago

    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's avatar
      PijushRoy
      Community Champion
      3 years ago

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

      • vanessafvg's avatar
        vanessafvg
        Community Champion
        3 years ago

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

  • Ashish_Mathur's avatar
    Ashish_Mathur
    Super User
    3 years ago

    Hi,

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

  • vanessafvg's avatar
    vanessafvg
    Community Champion
    3 years ago

    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's avatar
      PijushRoy
      Community Champion
      3 years ago

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

      • vanessafvg's avatar
        vanessafvg
        Community Champion
        3 years ago

        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

  • MFelix's avatar
    MFelix
    Super User
    3 years ago

    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's avatar
      PijushRoy
      Community Champion
      3 years ago

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

      • MFelix's avatar
        MFelix
        Super User
        3 years ago

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

         

         

    • rsbin's avatar
      rsbin
      Community Champion
      3 years ago

      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's avatar
        PijushRoy
        Community Champion
        3 years ago

        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