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]),   ...
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

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

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

It's not working for me.

 

I've created a test dataset with test numbers (including 2017 data) to see how it'd work once I get my hands on 2017 data as well.

It's giving me very weird results both using the 1st proposed sollution and the 2nd.

 

please see for yourself, I'm sharing the test PowerBI Desktop file: https://drive.google.com/a/piazzaweb.nl/file/d/0B741KF-Q0yK6VjkxT0xrdUprNkJGcWduelJ0empTZG9mXy1N/view?usp=drivesdk 

I have included a picture from Excel with what the correct answer should be.

 

 

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

    I have found something that works: https://javierguillen.wordpress.com/2011/09/13/calculating-moving-averages-in-powerpivot-dax/

     

    as result for that Test table thing it gives this, and it actually works. (let's hope that it'll work for the actual file as well)

     

    Attempt3 = if(COUNTROWS(values(Sheet1[month]))=1,
    calculate(
    sum(Sheet1[Amount]) / COUNTROWS(values(Sheet1[month])),
    DATESBETWEEN(
    Sheet1[Date],
    FIRSTDATE(PARALLELPERIOD(Sheet1[Date],
    -5, MONTH)),
    LASTDATE(parallelperiod(Sheet1[Date],0, MONTH))
    ),all(Sheet1)
    ))