Forum Discussion

MrMarshall's avatar
MrMarshall
Helper II
7 years ago
Solved

Quick measure rolling average shows future dates

Today, I have a separate Date table and a Sales table.

This time I was creating a rolling average with a quick measure, using the Sales amount field in the sales table and the primary Date field on the date table. 

 

The problem is when I am trying to filter the year using a slicer, the rolling average graph is showing 12 month forward. This makes sense since it got data from 12 months back. But how do I stop it from showing dates 12m forward?

I can't figure this one out. 

 

Instead of pasting the examples in here, I have shared a simple Power BI file in the link below.

https://thelindata-my.sharepoint.com/:u:/g/personal/andreas_marshall_thelindata_se/EQ05SHWuT2FNlUHK0-_9IgUBvga33h4cIAnNnhY7cTmOdQ?e=wRXa2z

 

The screenshow shows the problem: 

Need Help

3 Replies

      • Anonymous's avatar
        Anonymous
        Not applicable
        5 years ago

        How can I do that in my case as shown below?

        I don't need the future dates (oct to dec 2020):

         

        My DAX for the one measure used in this axis:

         

        Count of events 6 MO =
        IF(
            ISFILTERED('tb_aog'[INICIO]),
            ERROR("Time intelligence quick measures can only be grouped or filtered by the Power BI-provided date hierarchy or primary date column."),
            VAR __LAST_DATE = ENDOFMONTH('tb_aog'[INICIO].[Date])
            VAR __DATE_PERIOD =
                DATESBETWEEN(
                    'tb_aog'[INICIO].[Date],
                    STARTOFMONTH(DATEADD(__LAST_DATE, -5, MONTH)),
                    ENDOFMONTH(DATEADD(__LAST_DATE, 0, MONTH))
                )
            RETURN
        IF (
        YEAR ( __LAST_DATE ) IN ALLSELECTED ( tb_aog[INICIO].[Year] ),
                AVERAGEX(
                    CALCULATETABLE(
                        SUMMARIZE(
                            VALUES('tb_aog'),
                            'tb_aog'[INICIO].[Year],
                            'tb_aog'[INICIO].[QuarterNo],
                            'tb_aog'[INICIO].[Quarter],
                            'tb_aog'[INICIO].[MonthNo],
                            'tb_aog'[INICIO].[Month]
                        ),
                        __DATE_PERIOD
                    ),
                    CALCULATE([Count of events], ALL('tb_aog'[INICIO].[Day]))
                )
        ))

         

         

         

         

        Thanks in advance.

        Marcos