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

     

     

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