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

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

3 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    7 years ago

    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's avatar
      viveksasi94
      Helper I
      7 years ago

      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.

  • 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