topic Re: Need Help on Dax : Concatenx , Filter for previous month and next 3 months and calculating in DAX Commands and Tips

Need Help on Dax : Concatenx , Filter for previous month and next 3 months and calculating

swasim — Mon, 03 Apr 2023 13:56:55 GMT

Experts :

I am new to power bi , trying to figure out a solution for the below problem where clients wants.

(Note: I did previous post similar question but expected output was incorrect)

E.g: Data table.

enddate	status	product	ID
1/2/2022	Slipped	Lenova	12
1/10/2022	Slipped	Microsoft	13
3/10/2023	Slipped	Microsoft	1
3/30/2023	Ontrack	Microsoft	16
4/10/2023	Ontrack	Lenova	17
4/17/2023	Risk	Lenova	2
5/31/2023	Risk	Microsoft	4
5/30/2023	Ontrack	HP	15
5/10/2023	Ontrack	Lenova	19
11/10/2023	Risk	Microsoft	21
12/10/2023	InProgress	HP	22

Expected Output

Grouped by Product
Due : Previous 3 months to be displayed with "/" seperated (concatenx ) and Current Month (April) and Next 2 months to be displayed with "/" seperated
total : Count the ID column based on the product and endate
status : display status with respective count in the bracket and color based on the condition.

Please attach the pbix file , anyone if you could resolve.

Jihwan_Kim tamerj1

Re: Need Help on Dax : Concatenx , Filter for previous month and next 3 months and calculating

amitchandak — Mon, 03 Apr 2023 15:54:15 GMT

swasim , Have date table there you can create columns like (join with date of your table)

month = format([Date], "mmm")

Qtr = format([Date] , "YYYY\QQ")

Month concat = "Due " & concatenatex(Filter(Date, [qtr] = earlier([qtr]) ), [month], "/")

and use this Month concat in visual

Re: Need Help on Dax : Concatenx , Filter for previous month and next 3 months and calculating

snarasi_83 — Tue, 04 Apr 2023 15:44:24 GMT

Tried with the above aproach , doesnt work also . it displays all months repeated concatened

Re: Need Help on Dax : Concatenx , Filter for previous month and next 3 months and calculating

tamerj1 — Tue, 04 Apr 2023 16:20:18 GMT

snarasi_83

Based on what are you grouping two statuses to aggregate the values of Due and Total?

Re: Need Help on Dax : Concatenx , Filter for previous month and next 3 months and calculating

snarasi_83 — Tue, 04 Apr 2023 16:49:54 GMT

Grouping is done by Product and endate.

Anything less than currect month is grouped. ( Total is for each product and group for this period)
Current month and future 2 months grouped ( Total is for each product and group for this period)

Ignore Total Column . If you can figure out Status and Due. I should be good.

Attaching the data table

enddate	status	product	ID
1/2/2022	Slipped	Lenova	12
1/10/2022	Slipped	Microsoft	13
3/10/2023	Slipped	Microsoft	1
3/30/2023	Ontrack	Microsoft	16
4/10/2023	Ontrack	Lenova	17
4/17/2023	Risk	Lenova	2
5/31/2023	Risk	Microsoft	4
5/30/2023	Ontrack	HP	15
5/10/2023	Ontrack	Lenova	19
11/10/2023	Risk	Microsoft	21
12/10/2023	InProgress	HP	22

Re: Need Help on Dax : Concatenx , Filter for previous month and next 3 months and calculating

tamerj1 — Tue, 04 Apr 2023 17:39:04 GMT

snarasi_83

Are you ok with calculated columns solution?

Re: Need Help on Dax : Concatenx , Filter for previous month and next 3 months and calculating

swasim — Tue, 04 Apr 2023 17:51:56 GMT

Yes , calculated column or measure . Anything should be fine .

Re: Need Help on Dax : Concatenx , Filter for previous month and next 3 months and calculating

tamerj1 — Tue, 04 Apr 2023 18:23:18 GMT

swasim

I Don't know where June and July months came from. Besides you mentioned only 3 future months. However, please try the first colum if of I will proceed with next. Also I could not match Total results with the presented expected results. Please present expected results that mayches the provided sample that would greatly help understanding your business logic

Due =
VAR CurrentEndDate = 'Table'[EndDate]
VAR CurrentYearMonth =
YEAR ( 'Table'[EndDate] ) * 12
+ MONTH ( 'Table'[EndDate] )
VAR TodayYearMonth =
YEAR ( TODAY () ) * 12
+ MONTH ( TODAY () )
VAR Dates =
CALCULATETABLE (
VALUES ( 'Table'[EndDate] ),
ALLEXCEPT ( 'Table', 'Table'[Product] )
)
VAR DatesAndMonths =
ADDCOLUMNS ( Dates, "@Month", MONTH ( 'Table'[EndDate] ) )
VAR SummaryTable =
GROUPBY (
DatesAndMonths,
[@Month],
"@MaxDate", MAXX ( CURRENTGROUP (), 'Table'[EndDate] )
)
VAR T1 =
FILTER ( SummaryTable, 'Table'[EndDate] <= TODAY () )
VAR T2 =
FILTER ( SummaryTable, 'Table'[EndDate] > TODAY () )
VAR Result1 =
CONCATENATEX ( T1, [@Month], "/", [@MaxDate], ASC )
VAR Result2 =
CONCATENATEX ( T2, [@Month], "/", [@MaxDate], ASC )
RETURN
IF (
CurrentYearMonth <= TodayYearMonth + 3,
"Due: "
& IF ( CurrentEndDate <= TODAY (), Result1, Result2 )
)

Re: Need Help on Dax : Concatenx , Filter for previous month and next 3 months and calculating

snarasi_83 — Tue, 04 Apr 2023 20:22:35 GMT

Sorry it was a typo. Attaching the table and expected results.

Previous is Jan/Feb/March

Futire is Current Month April and 2 months (April/May and June)

enddate	status	product	ID
12/2/2022	Slipped	Lenova	12
12/10/2022	InProgress	HP	22
1/2/2023	Slipped	Lenova	13
2/10/2023	Slipped	Microsoft	1
3/30/2023	Ontrack	Microsoft	16
4/10/2023	Ontrack	Lenova	17
4/17/2023	Risk	Lenova	2
5/10/2023	Ontrack	Lenova	19
5/30/2023	Ontrack	HP	15
5/31/2023	Risk	Microsoft	4
8/10/2023	Ontrack	Lenova	33
11/10/2023	Risk	Microsoft	21

Expected Result:

Re: Need Help on Dax : Concatenx , Filter for previous month and next 3 months and calculating

tamerj1 — Wed, 05 Apr 2023 05:40:30 GMT

snarasi_83
Apologies for the late response. It was too late last night and I could only use my mobile phone so I wasn't able to test.

Please refer to attached sample file and return back with your comments in order to amend.

Re: Need Help on Dax : Concatenx , Filter for previous month and next 3 months and calculating

snarasi_83 — Wed, 05 Apr 2023 20:02:54 GMT

tamerj1 Excellent Implementation . You are a Rockstar

Can you check this one issue.

95 % resolved only filtering for 6 months data (Jan - June )is missing. it was taking full year data from table instead of filter below condition

Jan/Feb/ March - (Endate > Previous2Month <= CurrMonth)

April/May/June as Future (Endate > CurrMonth and Endate <= Next2Months )

I tried updating the measure but table data is incorrect.

Due = VAR CurrentEndDate = 'Table'[EndDate] VAR Previous2Month = EOMONTH ( TODAY (), -4 ) VAR CurrMonth = EOMONTH ( TODAY (), -1 ) VAR Next2Months = EOMONTH ( TODAY (), 2 ) VAR Dates = CALCULATETABLE ( VALUES ( 'Table'[EndDate] ), ALLEXCEPT ( 'Table', 'Table'[Product] ) ) VAR DatesAndMonths = ADDCOLUMNS ( Dates, "@Month", FORMAT ( 'Table'[EndDate], "MMMM" ) ) VAR SummaryTable = GROUPBY ( DatesAndMonths, [@Month], "@MaxDate", MAXX ( CURRENTGROUP (), 'Table'[EndDate] ) ) VAR T1 = FILTER ( SummaryTable, [@MaxDate] > Previous2Month && [@MaxDate] <= CurrMonth) VAR T2 = FILTER ( SummaryTable, [@MaxDate] > CurrMonth && [@MaxDate] <= Next2Months) VAR Result1 = CONCATENATEX ( T1, [@Month], "/", [@MaxDate], ASC ) VAR Result2 = CONCATENATEX ( T2, [@Month], "/", [@MaxDate], ASC ) RETURN "Due: " & IF ( CurrentEndDate <= CurrMonth, Result1, Result2 )

Re: Need Help on Dax : Concatenx , Filter for previous month and next 3 months and calculating

tamerj1 — Thu, 06 Apr 2023 03:51:08 GMT

snarasi_83
Please see attached updated sample file

Re: Need Help on Dax : Concatenx , Filter for previous month and next 3 months and calculating

snarasi_83 — Thu, 06 Apr 2023 12:03:49 GMT

tamerj1 There is a issue , if you look at the table u posted above . MIcrosfoft product has 3 records between Jan to May in data . But in table UI shows only 1 record for Risk. Ontrack and Slipped is missing .

Re: Need Help on Dax : Concatenx , Filter for previous month and next 3 months and calculating

tamerj1 — Thu, 06 Apr 2023 12:39:36 GMT

snarasi_83

Jan is filtered out. You asked for 3 months before current month and 3 months after. That would be Feb to July. However, you csn play with +3/-3 to adjust the period as you wish.

Re: Need Help on Dax : Concatenx , Filter for previous month and next 3 months and calculating

snarasi_83 — Fri, 07 Apr 2023 02:33:18 GMT

Excellent tamerj1 . Resolved , i got what i need. Appreciate your time and patience in solving this.