Forum Discussion
Properly group the date
Hi,
I have data like below.
Now, I want to create a visual which gonna show at Axis grouped date.
These groups would be respectively: 30+, 15-30, 8-14, 1-7, overdue.
Dates would depend on TODAY()'s date difference.
Now, at Values I want to Count numbers of companies that belong to an appropriate group.
Companies cannot duplicate! -> I mean by that if the same company has a few different dates I want to show the earliest.
Hi pawelj795 ,
If I got it correctly, you can follow these steps to try:
1. Create the first measure to calculate datediff:
Date diff = DATEDIFF ( CALCULATE ( MIN ( 'Table'[Date] ), ALLEXCEPT ( 'Table', 'Table'[Company] ) ), TODAY (), DAY )2. Create a new table included '30+', '15-30', '8-14', '1-7', 'Overdue' columns, just enter data manually
3. Create the second measure to get the expected result:
Count = VAR tab = SUMMARIZE ( 'Table', [Company], "Earliest_Date", MIN ( 'Table'[Date] ), "Datediff", [Date diff] ) VAR newtab = ADDCOLUMNS ( tab, "1-7", IF ( [Date diff] >= 1 && [Date diff] <= 7, 1, 0 ), "8-14", IF ( [Date diff] >= 8 && [Date diff] <= 14, 1, 0 ), "15-30", IF ( [Date diff] >= 15 && [Date diff] <= 30, 1, 0 ), "30+", IF ( [Date diff] >= 30, 1, 0 ), "OverDue", IF ( [Date diff] < 1 || [Date diff] = BLANK (), 1, 0 ) ) VAR _group = SELECTEDVALUE ( Test[Group] ) RETURN SWITCH ( _group, "1-7", COUNTROWS ( FILTER ( newtab, [1-7] = 1 ) ), "8-14", COUNTROWS ( FILTER ( newtab, [8-14] = 1 ) ), "15-30", COUNTROWS ( FILTER ( newtab, [15-30] = 1 ) ), "30+", COUNTROWS ( FILTER ( newtab, [30+] = 1 ) ), "OverDue", COUNTROWS ( FILTER ( newtab, [OverDue] = 1 ) ), BLANK () ) Here is my sample file hope to help you, please try it: PBIX
Best Regards,
Yingjie LiIf this post helps then please consider Accept it as the solution to help the other members find it more quickly.
2 Replies
If you post sample data as text and expected result, can probably be more accurate, Please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490
But perhaps something along the lines of:
Measure =
VAR __Table = SUMMARIZE('Table',[Company],"Min_Date",MIN([Date]))
RETURN
<Some calculation over __Table>
Hi pawelj795 ,
If I got it correctly, you can follow these steps to try:
1. Create the first measure to calculate datediff:
Date diff = DATEDIFF ( CALCULATE ( MIN ( 'Table'[Date] ), ALLEXCEPT ( 'Table', 'Table'[Company] ) ), TODAY (), DAY )2. Create a new table included '30+', '15-30', '8-14', '1-7', 'Overdue' columns, just enter data manually
3. Create the second measure to get the expected result:
Count = VAR tab = SUMMARIZE ( 'Table', [Company], "Earliest_Date", MIN ( 'Table'[Date] ), "Datediff", [Date diff] ) VAR newtab = ADDCOLUMNS ( tab, "1-7", IF ( [Date diff] >= 1 && [Date diff] <= 7, 1, 0 ), "8-14", IF ( [Date diff] >= 8 && [Date diff] <= 14, 1, 0 ), "15-30", IF ( [Date diff] >= 15 && [Date diff] <= 30, 1, 0 ), "30+", IF ( [Date diff] >= 30, 1, 0 ), "OverDue", IF ( [Date diff] < 1 || [Date diff] = BLANK (), 1, 0 ) ) VAR _group = SELECTEDVALUE ( Test[Group] ) RETURN SWITCH ( _group, "1-7", COUNTROWS ( FILTER ( newtab, [1-7] = 1 ) ), "8-14", COUNTROWS ( FILTER ( newtab, [8-14] = 1 ) ), "15-30", COUNTROWS ( FILTER ( newtab, [15-30] = 1 ) ), "30+", COUNTROWS ( FILTER ( newtab, [30+] = 1 ) ), "OverDue", COUNTROWS ( FILTER ( newtab, [OverDue] = 1 ) ), BLANK () ) Here is my sample file hope to help you, please try it: PBIX
Best Regards,
Yingjie LiIf this post helps then please consider Accept it as the solution to help the other members find it more quickly.
