Dax Time Intelligence Functions Weird behavior - 13 Month Rolling Calc

Hansie151 

avg_13m_cls_stck_wac = 
VAR LastVisibleDate = MAX('dim_dates'[day])
VAR LastSalesDate = CALCULATE(MAX('FactMonthly'[fin_ldom]), ALL('FactMonthly'))
VAR PeriodStart = EDATE(LastVisibleDate, -13)
RETURN
IF(
LastVisibleDate > LastSalesDate, 
BLANK(),
CALCULATE(
AVERAGEX(VALUES('dim_dates'[fin_yr_mt]), [SUM_Cls_Stck_Total] + 0),
'dim_dates'[day] >= PeriodStart,
'dim_dates'[day] <= LastVisibleDate
)
)

Hi All

I'm hoping someone can help, I created a retail calendar and am trying to use the timeintelligence functions to calculate 13 month avg.

The timeintelligence functions work perfectly if I just go back 12 months or less, but when going back more than 12 months (i.e -13) I get strange results, some months calcs are only 2 periods long some 5 periods etc..? (I have setup the calendar to a day level and i'm getting no validation errors)

Any advice?

avg_13m_cls_stck_wac = 
VAR LastVisibleDate = MAX ( 'dim_dates'[day] )
-- Find the absolute last day of actual data available in the Fact table
VAR LastSalesDate = 
    CALCULATE(
        MAX('FactMonthly'[fin_ldom]), 
        ALL('FactMonthly')
    )
VAR AverageCalc = 
    CALCULATE (
        AVERAGEX( 
            VALUES ( 'dim_dates'[fin_yr_mt] ), 
            [SUM_Cls_Stck_Total] + 0 
        ),
        DATESINPERIOD (
            'Retail', 
            LastVisibleDate,
            -13,
            MONTH
        )
    )

RETURN
    IF (
        LastVisibleDate > LastSalesDate, 
        BLANK(), 
        AverageCalc
    )

Hi Hansie151,

so you are using the new time intelligence functions, calendar-based.

That heavily depends on the Calendar definition, so can you please share the pbix file? Via a cloud service or via a private message to me?

Otherwise, the standard answer applies (but in reality I also need the calendar definition and the dates table):

Please include, in a usable format, not an image, a small set of rows for each of the tables involved in your request and show the data model in a picture, so that we can import the tables in Power BI and reproduce the data model. The subset of rows you provide, even is just a subset of the original tables, must cover your issue or question completely. Alternatively, you can share your .pbix via some cloud service and paste the link here. Do not include sensitive information and do not include anything that is unrelated to the issue or question. Please show the expected outcome based on the sample data you provided and make sure, in case you show a Power BI visual, to clarify the columns used in the grouping sections of the visual.

Need help uploading data? click here

Want faster answers? click here

If this helped, please consider giving kudos and mark as a solution

me in replies or I'll lose your thread

Want to check your DAX skills? Answer my biweekly DAX challenges on the kubisco Linkedin page

Consider voting this Power BI idea

Francesco Bergamaschi

MBA, M.Eng, M.Econ, Professor of BI

Hi Hansie151,

I made some tests and I think I got the problem understood. We need to fix the DATESINPERIOD optional parameters. I assume you have an issue everytime the 13-th prior month has a bigger nr of days vs the current one.

Can you send me your pbix via private message?

If this helped, please consider giving kudos and mark as a solution

@me in replies or I'll lose your thread

Want to check your DAX skills? Answer my biweekly DAX challenges on the kubisco Linkedin page

Consider voting this Power BI idea

Francesco Bergamaschi

MBA, M.Eng, M.Econ, Professor of BI

Hansie151 Please check the use of week in your report. I suggest trying with the Week-based calculations instead of normal calendar base formulas. 

Not all month/years have the same number of weeks, therefore you can have different week-month configurations between different years and the last date of each aggregated period by weeks can be different.

Please check:
https://powerbi.microsoft.com/es-es/blog/calendar-based-time-intelligence-time-intelligence-tailored-preview/

https://www.sqlbi.com/articles/introducing-calendar-based-time-intelligence-in-dax/

https://www.sqlbi.com/articles/using-weekly-calendars-in-power-bi/

Hello Hansie151,

We hope you're doing well. Could you please confirm whether your issue has been resolved or if you're still facing challenges? Your update will be valuable to the community and may assist others with similar concerns.

Thank you.