cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
viveksasi94 Regular Visitor
Regular Visitor

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 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

1 ACCEPTED SOLUTION

Accepted Solutions
Community Support Team
Community Support Team

Re: Alternate option for DATESBETWEEN function for Direct Query mode

Hi @viveksasi94

Assume your data is like

10.png

 

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]

11.png

12.png

 

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]

13.png

 

Best Regards

Maggie

3 REPLIES 3
Highlighted
chirayuw Member
Member

Re: Alternate option for DATESBETWEEN function for Direct Query mode

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 Regular Visitor
Regular Visitor

Re: Alternate option for DATESBETWEEN function for Direct Query mode

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.

Capture.PNG

 

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

Community Support Team
Community Support Team

Re: Alternate option for DATESBETWEEN function for Direct Query mode

Hi @viveksasi94

Assume your data is like

10.png

 

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]

11.png

12.png

 

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]

13.png

 

Best Regards

Maggie