Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Olia
Helper III
Helper III

date type not recognized as such in DAX

Hi everyone,

 

I'm getting a weird error:

 

 

date3.png

 

 

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?

 

1 ACCEPTED SOLUTION
v-juanli-msft
Community Support
Community Support

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

View solution in original post

5 REPLIES 5
v-juanli-msft
Community Support
Community Support

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

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 - Monthrolling average2.png

 

 

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?

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]

12.png

 

Best Regards

Maggie

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/vie... 

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

 

 

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

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.