Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
swasim
Frequent Visitor

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

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.

 

enddatestatusproductID
1/2/2022SlippedLenova12
1/10/2022SlippedMicrosoft13
3/10/2023SlippedMicrosoft1
3/30/2023OntrackMicrosoft16
4/10/2023OntrackLenova17
4/17/2023RiskLenova2
5/31/2023RiskMicrosoft4
5/30/2023OntrackHP15
5/10/2023OntrackLenova19
11/10/2023RiskMicrosoft21
12/10/2023InProgressHP22

 

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.
  •  

swasim_0-1680529505051.png

 

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

@Jihwan_Kim @tamerj1 

1 ACCEPTED SOLUTION

@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.

1.png

View solution in original post

14 REPLIES 14
tamerj1
Super User
Super User

@snarasi_83 

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

Grouping is done by Product and endate.

  1.    Anything less than currect month is grouped.  ( Total is for each product and group for this period)
  2.    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

 

enddatestatusproductID
1/2/2022SlippedLenova12
1/10/2022SlippedMicrosoft13
3/10/2023SlippedMicrosoft1
3/30/2023OntrackMicrosoft16
4/10/2023OntrackLenova17
4/17/2023RiskLenova2
5/31/2023RiskMicrosoft4
5/30/2023OntrackHP15
5/10/2023OntrackLenova19
11/10/2023RiskMicrosoft21
12/10/2023InProgressHP22

@snarasi_83 

Are you ok with calculated columns solution?

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

@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 )
)

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)

 

enddatestatusproductID
12/2/2022SlippedLenova12
12/10/2022InProgressHP22
1/2/2023SlippedLenova13
2/10/2023SlippedMicrosoft1
3/30/2023OntrackMicrosoft16
4/10/2023OntrackLenova17
4/17/2023RiskLenova2
5/10/2023OntrackLenova19
5/30/2023OntrackHP15
5/31/2023RiskMicrosoft4
8/10/2023OntrackLenova33
11/10/2023RiskMicrosoft21

 

Expected Result:

snarasi_83_0-1680639660622.png

 

@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.

1.png

@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 )

 

 

 

@snarasi_83 
Please see attached updated sample file

1.png2.png

@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 .

@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. 

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

amitchandak
Super User
Super User

@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

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

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors