Properly group the date

[pawelj795]

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.

[v-yingjl · Accepted solution]

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 Li

If this post helps then please consider Accept it as the solution to help the other members find it more quickly.

[Greg_Deckler]

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>