Reply
Regular Visitor
Posts: 17
Registered: ‎11-01-2018
Accepted Solution

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


Accepted Solutions
Community Support Team
Posts: 2,490
Registered: ‎03-15-2018

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

View solution in original post


All Replies
Member
Posts: 65
Registered: ‎10-03-2018

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

Regular Visitor
Posts: 17
Registered: ‎11-01-2018

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
Posts: 2,490
Registered: ‎03-15-2018

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