Forum Discussion

Olia's avatar
Olia
Icon for Advocate II rankAdvocate II
8 years ago
Solved

date type not recognized as such in DAX

Hi everyone,

 

I'm getting a weird error:

 

 

 

 

Here's the calculated measure that I'm using:

 

Rolling average 6m GC =
CALCULATE (
    AVERAGEX ('Sheet,'Sheet'[Group Amount]),
    DATESINPERIOD ('Sheet'[Date].[MonthNo],
        LASTDATE ( 'Sheet'[Date].[MonthNo]),
        -6,
        MONTH))

 

 

 

What am I missing? Will running around and screaming help?

 

Need Help
  • v-juanli-msft's avatar
    v-juanli-msft
    8 years ago

    Hi Olia

    For the function LASTDATE, please pay attention to the following:

    LASTDATE(<dates>)

    The dates argument can be any of the following:

    • A reference to a date/time column,

    • A table expression that returns a single column of date/time values,

    • A Boolean expression that defines a single-column table of date/time values.

    So try this formula instead

    Rolling average 6m GC =
CALCULATE (
    AVERAGEX ('Sheet,'Sheet'[Group Amount]),
    DATESINPERIOD ('Sheet'[Date].[MonthNo],
        LASTDATE ( 'Sheet'[Date]),
        -6,
        MONTH))

     

    Best Regards

    Maggie

5 Replies

  • v-juanli-msft's avatar
    v-juanli-msft
    Icon for Community Support rankCommunity Support
    8 years ago

    Hi Olia

    For the function LASTDATE, please pay attention to the following:

    LASTDATE(<dates>)

    The dates argument can be any of the following:

    • A reference to a date/time column,

    • A table expression that returns a single column of date/time values,

    • A Boolean expression that defines a single-column table of date/time values.

    So try this formula instead

    Rolling average 6m GC =
CALCULATE (
    AVERAGEX ('Sheet,'Sheet'[Group Amount]),
    DATESINPERIOD ('Sheet'[Date].[MonthNo],
        LASTDATE ( 'Sheet'[Date]),
        -6,
        MONTH))

     

    Best Regards

    Maggie

    • Olia's avatar
      Olia
      Icon for Advocate II rankAdvocate II
      8 years ago

      Hi Maggie,

       

      Thank you for your help! I have used your formula, but I am still doing something wrong though, and have no clue what...

       

      Item - Amount - Rolling average - Q - Month

       

       

      according to my calculations, it should be:

      June = (87+101+147+200+234+133)/6 = 150.318 and not 11

      July (101+147+200+234+133+72) / 6 = 147.733 and not 18

       

      whyyyyyy?

      • v-juanli-msft's avatar
        v-juanli-msft
        Icon for Community Support rankCommunity Support
        8 years ago

        Hi Olia

        Try these measures

        Measure =
SUMX (
    FILTER (
        ALL ( Sheet6 ),
        [month]
            >= MAX ( [month] ) - 5
            && [month] <= MAX ( [month] )
    ),
    [AMOUNT]
)

Measure 2 =
CALCULATE (
    DISTINCTCOUNT ( Sheet6[month] ),
    FILTER (
        ALL ( Sheet6 ),
        [month]
            >= MAX ( [month] ) - 5
            && [month] <= MAX ( [month] )
    )
)

Measure 3 = [Measure]/[Measure 2]

         

        Best Regards

        Maggie