Forum Discussion

KevinGesquiere's avatar
KevinGesquiere
Frequent Visitor
8 years ago
Solved

Table with measures over different periods

Is it possible to show a table with measures over different periods? It probably does, but how should I get started? To be more precise, the data is coming from an orders table where we want an over...
Need Help
  • v-jiascu-msft's avatar
    v-jiascu-msft
    8 years ago

    Hi KevinGesquiere,

     

    Please check out the demo in the attachment.

    1. These conditions aren't in the source table, we can create one.

    Col                                            ID

    % open orders 1
    % closed orders < 5days 2
    % closed orders 5 - 15 days 3
    % closed orders > 15days 4

    2. We also need a date table.

     

    Calendar = CALENDARAUTO()

    3. Don't establish relationships.

    4. Add calculated column to calculate days.

    DaysUsed = DATEDIFF([Orderdate], [DateOrderClosed],DAY)

    5. Create three measures.

    Last Months =
VAR typeID =
    MAX ( Table2[ID] )
RETURN
    IF (
        typeID = 1,
        CALCULATE (
            COUNT ( Table1[Id] ),
            FILTER (
                'Table1',
                ISBLANK ( Table1[DateOrderClosed] ) = TRUE ()
                    && Table1[Orderdate] >= EOMONTH ( TODAY (), -1 )
            )
        ),
        IF (
            typeID = 2,
            CALCULATE (
                COUNT ( Table1[Id] ),
                FILTER (
                    'Table1',
                    ISBLANK ( Table1[DateOrderClosed] ) = FALSE ()
                        && Table1[Orderdate] >= EOMONTH ( TODAY (), -1 )
                        && Table1[DateOrderClosed] <= TODAY ()
                        && 'Table1'[DaysUsed] < 5
                )
            ),
            IF (
                typeID = 3,
                CALCULATE (
                    COUNT ( Table1[Id] ),
                    FILTER (
                        'Table1',
                        ISBLANK ( Table1[DateOrderClosed] ) = FALSE ()
                            && Table1[Orderdate] >= EOMONTH ( TODAY (), -1 )
                            && Table1[DateOrderClosed] <= TODAY ()
                            && 'Table1'[DaysUsed] <= 15
                            && 'Table1'[DaysUsed] >= 5
                    )
                ),
                IF (
                    typeid = 4,
                    CALCULATE (
                        COUNT ( Table1[Id] ),
                        FILTER (
                            'Table1',
                            ISBLANK ( Table1[DateOrderClosed] ) = FALSE ()
                                && Table1[Orderdate] >= EOMONTH ( TODAY (), -1 )
                                && Table1[DateOrderClosed] <= TODAY ()
                                && 'Table1'[DaysUsed] > 15
                        )
                    ),
                    0
                )
            )
        )
    )

     

     

    Last 6 Months =
VAR typeID =
    MAX ( Table2[ID] )
RETURN
    IF (
        typeID = 1,
        CALCULATE (
            COUNT ( Table1[Id] ),
            FILTER (
                'Table1',
                ISBLANK ( Table1[DateOrderClosed] ) = TRUE ()
                    && Table1[Orderdate] >= EOMONTH ( TODAY (), -6 )
            )
        ),
        IF (
            typeID = 2,
            CALCULATE (
                COUNT ( Table1[Id] ),
                FILTER (
                    'Table1',
                    ISBLANK ( Table1[DateOrderClosed] ) = FALSE ()
                        && Table1[Orderdate] >= EOMONTH ( TODAY (), -6 )
                        && Table1[DateOrderClosed] <= TODAY ()
                        && 'Table1'[DaysUsed] < 5
                )
            ),
            IF (
                typeID = 3,
                CALCULATE (
                    COUNT ( Table1[Id] ),
                    FILTER (
                        'Table1',
                        ISBLANK ( Table1[DateOrderClosed] ) = FALSE ()
                            && Table1[Orderdate] >= EOMONTH ( TODAY (), -6 )
                            && Table1[DateOrderClosed] <= TODAY ()
                            && 'Table1'[DaysUsed] <= 15
                            && 'Table1'[DaysUsed] >= 5
                    )
                ),
                IF (
                    typeid = 4,
                    CALCULATE (
                        COUNT ( Table1[Id] ),
                        FILTER (
                            'Table1',
                            ISBLANK ( Table1[DateOrderClosed] ) = FALSE ()
                                && Table1[Orderdate] >= EOMONTH ( TODAY (), -6 )
                                && Table1[DateOrderClosed] <= TODAY ()
                                && 'Table1'[DaysUsed] > 15
                        )
                    ),
                    0
                )
            )
        )
    )

    Last 2 Months =
VAR typeID =
    MAX ( Table2[ID] )
RETURN
    IF (
        typeID = 1,
        CALCULATE (
            COUNT ( Table1[Id] ),
            FILTER (
                'Table1',
                ISBLANK ( Table1[DateOrderClosed] ) = TRUE ()
                    && Table1[Orderdate] >= EOMONTH ( TODAY (), -2 )
            )
        ),
        IF (
            typeID = 2,
            CALCULATE (
                COUNT ( Table1[Id] ),
                FILTER (
                    'Table1',
                    ISBLANK ( Table1[DateOrderClosed] ) = FALSE ()
                        && Table1[Orderdate] >= EOMONTH ( TODAY (), -2 )
                        && Table1[DateOrderClosed] <= TODAY ()
                        && 'Table1'[DaysUsed] < 5
                )
            ),
            IF (
                typeID = 3,
                CALCULATE (
                    COUNT ( Table1[Id] ),
                    FILTER (
                        'Table1',
                        ISBLANK ( Table1[DateOrderClosed] ) = FALSE ()
                            && Table1[Orderdate] >= EOMONTH ( TODAY (), -2 )
                            && Table1[DateOrderClosed] <= TODAY ()
                            && 'Table1'[DaysUsed] <= 15
                            && 'Table1'[DaysUsed] >= 5
                    )
                ),
                IF (
                    typeid = 4,
                    CALCULATE (
                        COUNT ( Table1[Id] ),
                        FILTER (
                            'Table1',
                            ISBLANK ( Table1[DateOrderClosed] ) = FALSE ()
                                && Table1[Orderdate] >= EOMONTH ( TODAY (), -2 )
                                && Table1[DateOrderClosed] <= TODAY ()
                                && 'Table1'[DaysUsed] > 15
                        )
                    ),
                    0
                )
            )
        )
    )

    Best Regards,

    Dale

     

danb's avatar
danb
Icon for Resolver I rankResolver I
8 years ago

KevinGesquiere

Can you provide a sample of your data table?

KevinGesquiere's avatar
KevinGesquiere
Frequent Visitor
8 years ago

danb

It's just as an example, but here is some data (I'm not showing here all the fields from the original table). When the field DateOrderClosed is NULL then the order is still open.

 

IdOrderdateDateOrderClosed
11/05/201822/05/2018
210/05/20183/06/2018
32/06/20185/06/2018
45/06/2018NULL

 

Thanks

  • v-jiascu-msft's avatar
    v-jiascu-msft
    Icon for Microsoft Employee rankMicrosoft Employee
    8 years ago

    Hi KevinGesquiere,

     

    Please check out the demo in the attachment.

    1. These conditions aren't in the source table, we can create one.

    Col                                            ID

    % open orders 1
    % closed orders < 5days 2
    % closed orders 5 - 15 days 3
    % closed orders > 15days 4

    2. We also need a date table.

     

    Calendar = CALENDARAUTO()

    3. Don't establish relationships.

    4. Add calculated column to calculate days.

    DaysUsed = DATEDIFF([Orderdate], [DateOrderClosed],DAY)

    5. Create three measures.

    Last Months =
VAR typeID =
    MAX ( Table2[ID] )
RETURN
    IF (
        typeID = 1,
        CALCULATE (
            COUNT ( Table1[Id] ),
            FILTER (
                'Table1',
                ISBLANK ( Table1[DateOrderClosed] ) = TRUE ()
                    && Table1[Orderdate] >= EOMONTH ( TODAY (), -1 )
            )
        ),
        IF (
            typeID = 2,
            CALCULATE (
                COUNT ( Table1[Id] ),
                FILTER (
                    'Table1',
                    ISBLANK ( Table1[DateOrderClosed] ) = FALSE ()
                        && Table1[Orderdate] >= EOMONTH ( TODAY (), -1 )
                        && Table1[DateOrderClosed] <= TODAY ()
                        && 'Table1'[DaysUsed] < 5
                )
            ),
            IF (
                typeID = 3,
                CALCULATE (
                    COUNT ( Table1[Id] ),
                    FILTER (
                        'Table1',
                        ISBLANK ( Table1[DateOrderClosed] ) = FALSE ()
                            && Table1[Orderdate] >= EOMONTH ( TODAY (), -1 )
                            && Table1[DateOrderClosed] <= TODAY ()
                            && 'Table1'[DaysUsed] <= 15
                            && 'Table1'[DaysUsed] >= 5
                    )
                ),
                IF (
                    typeid = 4,
                    CALCULATE (
                        COUNT ( Table1[Id] ),
                        FILTER (
                            'Table1',
                            ISBLANK ( Table1[DateOrderClosed] ) = FALSE ()
                                && Table1[Orderdate] >= EOMONTH ( TODAY (), -1 )
                                && Table1[DateOrderClosed] <= TODAY ()
                                && 'Table1'[DaysUsed] > 15
                        )
                    ),
                    0
                )
            )
        )
    )

     

     

    Last 6 Months =
VAR typeID =
    MAX ( Table2[ID] )
RETURN
    IF (
        typeID = 1,
        CALCULATE (
            COUNT ( Table1[Id] ),
            FILTER (
                'Table1',
                ISBLANK ( Table1[DateOrderClosed] ) = TRUE ()
                    && Table1[Orderdate] >= EOMONTH ( TODAY (), -6 )
            )
        ),
        IF (
            typeID = 2,
            CALCULATE (
                COUNT ( Table1[Id] ),
                FILTER (
                    'Table1',
                    ISBLANK ( Table1[DateOrderClosed] ) = FALSE ()
                        && Table1[Orderdate] >= EOMONTH ( TODAY (), -6 )
                        && Table1[DateOrderClosed] <= TODAY ()
                        && 'Table1'[DaysUsed] < 5
                )
            ),
            IF (
                typeID = 3,
                CALCULATE (
                    COUNT ( Table1[Id] ),
                    FILTER (
                        'Table1',
                        ISBLANK ( Table1[DateOrderClosed] ) = FALSE ()
                            && Table1[Orderdate] >= EOMONTH ( TODAY (), -6 )
                            && Table1[DateOrderClosed] <= TODAY ()
                            && 'Table1'[DaysUsed] <= 15
                            && 'Table1'[DaysUsed] >= 5
                    )
                ),
                IF (
                    typeid = 4,
                    CALCULATE (
                        COUNT ( Table1[Id] ),
                        FILTER (
                            'Table1',
                            ISBLANK ( Table1[DateOrderClosed] ) = FALSE ()
                                && Table1[Orderdate] >= EOMONTH ( TODAY (), -6 )
                                && Table1[DateOrderClosed] <= TODAY ()
                                && 'Table1'[DaysUsed] > 15
                        )
                    ),
                    0
                )
            )
        )
    )

    Last 2 Months =
VAR typeID =
    MAX ( Table2[ID] )
RETURN
    IF (
        typeID = 1,
        CALCULATE (
            COUNT ( Table1[Id] ),
            FILTER (
                'Table1',
                ISBLANK ( Table1[DateOrderClosed] ) = TRUE ()
                    && Table1[Orderdate] >= EOMONTH ( TODAY (), -2 )
            )
        ),
        IF (
            typeID = 2,
            CALCULATE (
                COUNT ( Table1[Id] ),
                FILTER (
                    'Table1',
                    ISBLANK ( Table1[DateOrderClosed] ) = FALSE ()
                        && Table1[Orderdate] >= EOMONTH ( TODAY (), -2 )
                        && Table1[DateOrderClosed] <= TODAY ()
                        && 'Table1'[DaysUsed] < 5
                )
            ),
            IF (
                typeID = 3,
                CALCULATE (
                    COUNT ( Table1[Id] ),
                    FILTER (
                        'Table1',
                        ISBLANK ( Table1[DateOrderClosed] ) = FALSE ()
                            && Table1[Orderdate] >= EOMONTH ( TODAY (), -2 )
                            && Table1[DateOrderClosed] <= TODAY ()
                            && 'Table1'[DaysUsed] <= 15
                            && 'Table1'[DaysUsed] >= 5
                    )
                ),
                IF (
                    typeid = 4,
                    CALCULATE (
                        COUNT ( Table1[Id] ),
                        FILTER (
                            'Table1',
                            ISBLANK ( Table1[DateOrderClosed] ) = FALSE ()
                                && Table1[Orderdate] >= EOMONTH ( TODAY (), -2 )
                                && Table1[DateOrderClosed] <= TODAY ()
                                && 'Table1'[DaysUsed] > 15
                        )
                    ),
                    0
                )
            )
        )
    )

    Best Regards,

    Dale