Forum Discussion
EiA
3 years agoFrequent Visitor
Count clients based on recent payment history
Hi, anyone able to help me with the following problem? I have a basic invoice-table with this structure: Client Invoice Nr Date issued Paid/unpaid Amount X 1 01.05....
- 3 years ago
Hi,
Please check the below picture and the attached pbix file.
Problem one measure: = VAR _lasttwoinvoicenumber = CALCULATETABLE ( Data, TREATAS ( SUMMARIZE ( FILTER ( ADDCOLUMNS ( SUMMARIZE ( Data, Data[Client], Data[Invoice Nr] ), "toptwo", RANKX ( ALL ( Data[Invoice Nr] ), CALCULATE ( SUM ( Data[Invoice Nr] ) ), , DESC ) ), [toptwo] <= 2 ), Data[Client], Data[Invoice Nr] ), Data[Client], Data[Invoice Nr] ) ) VAR _countpaid = ADDCOLUMNS ( DISTINCT ( Data[Client] ), "countall", COUNTROWS ( FILTER ( _lasttwoinvoicenumber, Data[Client] = EARLIER ( Data[Client] ) ) ), "countpaid", COUNTROWS ( FILTER ( _lasttwoinvoicenumber, Data[Client] = EARLIER ( Data[Client] ) && Data[Paid/unpaid] = "Paid" ) ) ) RETURN COUNTROWS( FILTER ( _countpaid, [countall] = [countpaid] ) )Problem two measure: = VAR _lasttwoinvoicenumber = CALCULATETABLE ( Data, TREATAS ( SUMMARIZE ( FILTER ( ADDCOLUMNS ( SUMMARIZE ( Data, Data[Client], Data[Invoice Nr] ), "toptwo", RANKX ( ALL ( Data[Invoice Nr] ), CALCULATE ( SUM ( Data[Invoice Nr] ) ), , DESC ) ), [toptwo] <= 2 ), Data[Client], Data[Invoice Nr] ), Data[Client], Data[Invoice Nr] ) ) VAR _countpaid = ADDCOLUMNS ( DISTINCT ( Data[Client] ), "countall", COUNTROWS ( FILTER ( _lasttwoinvoicenumber, Data[Client] = EARLIER ( Data[Client] ) ) ), "countpaid", COUNTROWS ( FILTER ( _lasttwoinvoicenumber, Data[Client] = EARLIER ( Data[Client] ) && Data[Paid/unpaid] = "Paid" ) ) ) RETURN COUNTROWS( FILTER ( _countpaid, [countall] <> [countpaid] && [countpaid] = 1 ) )
Jihwan_Kim
Super User
3 years agoHi,
Please check the below picture and the attached pbix file.
Problem one measure: =
VAR _lasttwoinvoicenumber =
CALCULATETABLE (
Data,
TREATAS (
SUMMARIZE (
FILTER (
ADDCOLUMNS (
SUMMARIZE ( Data, Data[Client], Data[Invoice Nr] ),
"toptwo",
RANKX (
ALL ( Data[Invoice Nr] ),
CALCULATE ( SUM ( Data[Invoice Nr] ) ),
,
DESC
)
),
[toptwo] <= 2
),
Data[Client],
Data[Invoice Nr]
),
Data[Client],
Data[Invoice Nr]
)
)
VAR _countpaid =
ADDCOLUMNS (
DISTINCT ( Data[Client] ),
"countall",
COUNTROWS (
FILTER ( _lasttwoinvoicenumber, Data[Client] = EARLIER ( Data[Client] ) )
),
"countpaid",
COUNTROWS (
FILTER (
_lasttwoinvoicenumber,
Data[Client] = EARLIER ( Data[Client] )
&& Data[Paid/unpaid] = "Paid"
)
)
)
RETURN
COUNTROWS(
FILTER ( _countpaid, [countall] = [countpaid] )
)
Problem two measure: =
VAR _lasttwoinvoicenumber =
CALCULATETABLE (
Data,
TREATAS (
SUMMARIZE (
FILTER (
ADDCOLUMNS (
SUMMARIZE ( Data, Data[Client], Data[Invoice Nr] ),
"toptwo",
RANKX (
ALL ( Data[Invoice Nr] ),
CALCULATE ( SUM ( Data[Invoice Nr] ) ),
,
DESC
)
),
[toptwo] <= 2
),
Data[Client],
Data[Invoice Nr]
),
Data[Client],
Data[Invoice Nr]
)
)
VAR _countpaid =
ADDCOLUMNS (
DISTINCT ( Data[Client] ),
"countall",
COUNTROWS (
FILTER ( _lasttwoinvoicenumber, Data[Client] = EARLIER ( Data[Client] ) )
),
"countpaid",
COUNTROWS (
FILTER (
_lasttwoinvoicenumber,
Data[Client] = EARLIER ( Data[Client] )
&& Data[Paid/unpaid] = "Paid"
)
)
)
RETURN
COUNTROWS(
FILTER ( _countpaid, [countall] <> [countpaid] && [countpaid] = 1 )
)- EiA3 years agoFrequent Visitor
Excellent Jihwan_Kim , thank you!