Help with concatenatex

[rbreneman]

Hi!

I have a column chart visual as shown below. X-axis is Month Year from my date table. The numbers on the y-axis come from another table called Revenue Transactions, see example of table below. I want to write a measure and display it in a card on my report that tells the viewer whether any of the shown months are still in-progress. Based on the example table data below it would look something like "The following month(s) have not yet been closed: March 2023". It would need to support the possibility of multiple months being in progress, so listed as comma separated. The user could also change a date

filter and see 6 months instead of a quarter (3 months) in the column chart.

I'd appreciate anyone that could share an example of some DAX that would do this. I know I'll want to use concatenatex but I'm struggling with the filters to make it all work as expected.

Thanks so much!

Revenue Transactions Table:

Date	Revenue	Status
1/1/2023	10000	Committed
1/7/2023	50000	Committed
2/1/2023	49000	Committed
3/1/2023	22000	In Progress
3/9/2023	2000	Committed

Date Table:

Date	Month Year
1/1/2023	Jan 2023
1/7/2023	Jan 2023
2/1/2023	Feb 2023
3/1/2023	Mar 2023
3/9/2023	Mar 2023

[vicky_]

I used this measure, based on a variation of your provided data: 

Measure = 
var helper = ADDCOLUMNS(FILTER('Table', 'Table'[Status] = "In Progress"), "Month", EOMONTH('Table'[Date], 0))
return CONCATENATEX(
    DISTINCT(SELECTCOLUMNS(helper, "distinct month", [Month])), FORMAT([distinct month], "mmm yyyy"), ", "
)

[rbreneman]

@vicky_ , 
This is great, thank you so much!

This is working great for the months that are in the Revenue Transactions table. Where this isn't working is the current month of July and anything in the future. For example, there currently aren't any transactions loaded in the table with a July 2023 date, however July 2023 is in my date table and therefor selectable by the user (this is by design). If I expand my date slicer to show the following columns: Apr 2023, May 2023, Jun 2023, Jul 2023, and Aug 2023 (screenshot below). The measure only outputs June 2023 but I would want it to include months out of the date table that are missing from the revenue transactions table.

Is it possible to somehow include the date table dimension in this measure to account for months missing from the revenue transactions table?

Thanks so much for your help on this!

MonthCloseCard = 
VAR Helper = ADDCOLUMNS(FILTER('Revenue Transactions','Revenue Transactions'[Status] = "In Progress"), "Month", EOMONTH('Revenue Transactions'[Date], 0))
VAR Months = DISTINCT(SELECTCOLUMNS(Helper, "Distinct Month", [Month]))
VAR Solution = CONCATENATEX(
    Months, FORMAT([Distinct Month], "mmm yyyy"), ", "
)
Return
"The following months have not yet been closed: " & Solution & ". Data may not be accurate."

 

[vicky_ · Accepted solution]

In that case, what you can try is to add column to the date table instead of the other way around... 

So something like this:

MonthCloseCard = 
VAR Helper = ADDCOLUMNS(VALUES('Date'[Month]), "In Progress Count", CALCULATE(COUNTROWS(FILTER(Data, Data[Status] = "In Progress"))))
VAR Months = FILTER(Helper, [In Progress Count] > 0)
VAR Solution = CONCATENATEX(
    Months, FORMAT('Date'[Month], "mmm yyyy "), ", "
)
// return Solution
Return
"The following months have not yet been closed: " & Solution & ". Data may not be accurate."

I hope this helps!

[rbreneman]

Thank you!!!! This was so helpful! I was still missing the future months where I didn't have any data in my revenue transaction table. I was able to make a small change to what you provided and it's now working perfect. Basically added another column for count of committed status. I then filtered by in progress > 0 OR committed is blank.

MonthCloseCard = 
VAR Helper = ADDCOLUMNS(VALUES(DateTbl[Month Year]), "In Progress Count", CALCULATE(COUNTROWS(FILTER('Revenue Transactions','Revenue Transactions'[Status] = "In Progress"))), "Committed Count", CALCULATE(COUNTROWS(FILTER('Revenue Transactions','Revenue Transactions'[Status] = "Committed"))) )
VAR Months = FILTER(Helper, [In Progress Count] > 0 || [Committed Count] = BLANK() )
VAR Solution = CONCATENATEX(Months, [Month Year], ", " )
Return
Solution

Thanks again! I appreciate you taking the time to help me to get to this point.

[vicky_]

I'm glad you got it working!