Forum Discussion

viveksasi94's avatar
viveksasi94
Helper I
7 years ago
Solved

Alternate option for DATESBETWEEN function for Direct Query mode

Hello All, Please help me with an alternative DAX command as I'm in Direct Query Mode which does not support DATESBETWEEN function.   Two Weeks Ago = var todays_date = MAX('Supplier OTD'[Posting ...
Need Help
Show and Tell
  • v-juanli-msft's avatar
    v-juanli-msft
    7 years ago

    Hi viveksasi94

    Assume your data is like

     

    Create measures in the table (for example, two weeks ago)

    today =
CALCULATE (
    MAX ( [Posting Date in the Document] ),
    ALLEXCEPT ( 'Supplier OTD', 'Supplier OTD'[name] )
)

two weeks ago measure = [today]-14

otd_count =
CALCULATE (
    COUNT ( 'Supplier OTD'[OTD] ),
    FILTER (
        ALLEXCEPT ( 'Supplier OTD', 'Supplier OTD'[name] ),
        [OTD] = "on time"
            && [Posting Date in the Document] >= [two weeks ago]
            && [Posting Date in the Document] <= [today]
    )
)


otd_total =
CALCULATE (
    COUNT ( 'Supplier OTD'[OTD] ),
    FILTER (
        ALLEXCEPT ( 'Supplier OTD', 'Supplier OTD'[name] ),
        [Posting Date in the Document] >= [two weeks ago]
            && [Posting Date in the Document] <= [today]
    )
)


percent = [otd_count]/[otd_total]

     

    Or you could nest these measures into one measure

    Two Weeks ago =
VAR today =
    CALCULATE (
        MAX ( [Posting Date in the Document] ),
        ALLEXCEPT ( 'Supplier OTD', 'Supplier OTD'[name] )
    )
VAR twoweeksago = [today] - 14
VAR otd_count1 =
    CALCULATE (
        COUNT ( 'Supplier OTD'[OTD] ),
        FILTER (
            ALLEXCEPT ( 'Supplier OTD', 'Supplier OTD'[name] ),
            [OTD] = "on time"
                && [Posting Date in the Document] >= [two weeks ago measure]
                && [Posting Date in the Document] <= [today]
        )
    )
VAR otd_total1 =
    CALCULATE (
        COUNT ( 'Supplier OTD'[OTD] ),
        FILTER (
            ALLEXCEPT ( 'Supplier OTD', 'Supplier OTD'[name] ),
            [Posting Date in the Document] >= [two weeks ago measure]
                && [Posting Date in the Document] <= [today]
        )
    )
RETURN
    [otd_count] / [otd_total]

     

    Best Regards

    Maggie

v-juanli-msft's avatar
v-juanli-msft
Community Support
7 years ago

Hi viveksasi94

Assume your data is like

 

Create measures in the table (for example, two weeks ago)

today =
CALCULATE (
    MAX ( [Posting Date in the Document] ),
    ALLEXCEPT ( 'Supplier OTD', 'Supplier OTD'[name] )
)

two weeks ago measure = [today]-14

otd_count =
CALCULATE (
    COUNT ( 'Supplier OTD'[OTD] ),
    FILTER (
        ALLEXCEPT ( 'Supplier OTD', 'Supplier OTD'[name] ),
        [OTD] = "on time"
            && [Posting Date in the Document] >= [two weeks ago]
            && [Posting Date in the Document] <= [today]
    )
)


otd_total =
CALCULATE (
    COUNT ( 'Supplier OTD'[OTD] ),
    FILTER (
        ALLEXCEPT ( 'Supplier OTD', 'Supplier OTD'[name] ),
        [Posting Date in the Document] >= [two weeks ago]
            && [Posting Date in the Document] <= [today]
    )
)


percent = [otd_count]/[otd_total]

 

Or you could nest these measures into one measure

Two Weeks ago =
VAR today =
    CALCULATE (
        MAX ( [Posting Date in the Document] ),
        ALLEXCEPT ( 'Supplier OTD', 'Supplier OTD'[name] )
    )
VAR twoweeksago = [today] - 14
VAR otd_count1 =
    CALCULATE (
        COUNT ( 'Supplier OTD'[OTD] ),
        FILTER (
            ALLEXCEPT ( 'Supplier OTD', 'Supplier OTD'[name] ),
            [OTD] = "on time"
                && [Posting Date in the Document] >= [two weeks ago measure]
                && [Posting Date in the Document] <= [today]
        )
    )
VAR otd_total1 =
    CALCULATE (
        COUNT ( 'Supplier OTD'[OTD] ),
        FILTER (
            ALLEXCEPT ( 'Supplier OTD', 'Supplier OTD'[name] ),
            [Posting Date in the Document] >= [two weeks ago measure]
                && [Posting Date in the Document] <= [today]
        )
    )
RETURN
    [otd_count] / [otd_total]

 

Best Regards

Maggie