Forum Discussion
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...
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] ) )
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 )
)
Hi MFelix
Small mismatch
It is taking Index 4 rows Invoice number inplace of Index 25 Invoice noHi 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] ) )
