ConcatenateX in asymmetrical visual

[DataNinja777]

Hello Power BI community,

I'd like to create a table of numerical month by month financial statement numbers and at the far right of the table, I'd like to add the name of the bookkeeperswho recorded those FS entries using ConcatenateX.  The problem I am facing is that even though I filtered the concatenateX result, I get bookkeepers names in all the year month even though I'd like it to show only for the last month and do not repeat in all the month year columns.  (It is asymetrical requirement where numerical numbers are shown for all the month-year and bookkeeper names and number of rows entered are only shown for the last month to check which bookkeeping has not been completed for the month. I'd appreciate it if you could let me konw how I can achieve this objective.  The issue I am facing is that I cannot use "if" function to filter out all the other months as blank, as I cannot use month-year field without aggregation function.  

Thank you.

[v-binbinyu-msft · Accepted solution]

Hi @DataNinja777 ,

1. Create a Measure for Last Month's Bookkeepers
You can create a DAX measure that will calculate the ConcatenateX result only for the last month. This measure can then be used in your table visualization to display the names alongside your financial numbers. Here's a sample DAX formula you might use:

LastMonthBookkeepers =
VAR LastMonthEndDate =
    EOMONTH ( TODAY (), -1 )
VAR LastMonthStartDate =
    EOMONTH ( LastMonthEndDate, -1 ) + 1
RETURN
    IF (
        MAX ( FinancialStatementTable[Date] ) >= LastMonthStartDate
            && MAX ( FinancialStatementTable[Date] ) <= LastMonthEndDate,
        CONCATENATEX (
            FILTER (
                FinancialStatementTable,
                FinancialStatementTable[Date] >= LastMonthStartDate
                    && FinancialStatementTable[Date] <= LastMonthEndDate
            ),
            FinancialStatementTable[BookkeeperName],
            ", "
        ),
        BLANK ()
    )

Make sure to replace `FinancialStatementTable`, `Date`, and `BookkeeperName` with the actual names of your table and columns.

2. Add the Measure to Your Table Visualization
Add the measure you've created to the Values area of your table visualization. Since the measure returns BLANK for all periods other than the last month, it will only display the concatenated names for the last month.

3. Test the Solution
Ensure that your table is reflecting the correct information. The ConcatenateX measure should show the bookkeeper names only for the last month and be blank for all other months.

Best regards,
Community Support Team_Binbin Yu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.