Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
Please attach the pbix file , anyone if you could resolve.
Solved! Go to 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.
Based on what are you grouping two statuses to aggregate the values of Due and Total?
Grouping is done by Product and endate.
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 |
Yes , calculated column or measure . Anything should be fine .
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)
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:
@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.
@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 )
@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 .
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.
@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
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
41 | |
21 | |
21 | |
14 | |
13 |
User | Count |
---|---|
43 | |
36 | |
33 | |
18 | |
18 |