Forum Discussion

EiA's avatar
EiA
Frequent Visitor
3 years ago
Solved

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....
  • Jihwan_Kim's avatar
    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 )
    )