Alternate option for DATESBETWEEN function for Direct Query mode

[viveksasi94]

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 Date in the Document])
var two_weeks_ago_date = todays_date - 14
var otd_percent = IF(CALCULATE ( COUNTROWS('Supplier OTD'),'Supplier OTD'[OTD]="On Time")<>0 , CALCULATE ( COUNTROWS('Supplier OTD'),'Supplier OTD'[OTD]="On Time") / COUNT('Supplier OTD'[OTD]),0.05)
return
CALCULATE(otd_percent,
            DATESBETWEEN('Supplier OTD'[Posting Date in the Document],two_weeks_ago_date,todays_date)
)

 I'm trying to find OTD for the various time range. For example Two weeks ago, One month ago, One quarter ago as header and their OTD as rows values, but I'm getting the same value in all the three columns.

Thanks in advance

[v-juanli-msft · Accepted solution]

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

[Anonymous]

does the && argument work

CALCULATE(otd_percent,
            'Supplier OTD'[Posting Date in the Document] >= two_weeks_ago_date
  && 'Supplier OTD'[Posting Date in the Document] <= todays_date)
)

[viveksasi94]

Thanks for your contribution, but still I'm getting the same row values for all the three different time range. This is how I want it to

look like.

But now I'm getting the same percentage value for all the time periods for the above DAX command.