12 months back excluding current month and previous months

[schoden]

Hi Everyone , 

How can I achieve 12 months old data but exclude current month and previous month.   

For instance : Start from May 2020 to June 2019 excluding  August 2020  and July 2020.

Measure= DATESINPERIOD( Invoice Date, Today()-2, -12, Month) 

[nvprasad · Accepted solution]

Hi,

You can get using below Dax 🙂

VAR StartMonth =
    EOMONTH ( TODAY (), -17 ) + 1
VAR EndMonth =
    EOMONTH ( TODAY (), -2 ) + 1
RETURN

CALCULATE ([expression],
    FILTER (
        ALL ( 'Sample'[Invoice Date] ),
        'Sample'[Invoice Date] > StartMonth
            && 'Sample'[Invoice Date] < EndMonth
    ))

Appreciate a Kudos! 🙂
If this helps and resolves the issue, please mark it as a Solution! 🙂

Regards,
N V Durga Prasad

[Greg_Deckler · Accepted solution]

@schoden - You may find this helpful - https://community.powerbi.com/t5/Community-Blog/To-bleep-With-Time-Intelligence/ba-p/1260000

Also, see if my Time Intelligence the Hard Way provides a different way of accomplishing what you are going for.

https://community.powerbi.com/t5/Quick-Measures-Gallery/Time-Intelligence-quot-The-Hard-Way-quot-TIT...

[amitchandak · Accepted solution]

@schoden , Try with a date calendar

Rolling 12 = CALCULATE(sum(Sales[Sales Amount]),DATESINPERIOD('Date'[Date],ENDOFMONTH(Sales[Sales Date]),-12,MONTH))
Rolling 12 = CALCULATE(sum(Sales[Sales Amount]),DATESINPERIOD('Date'[Date],max(Sales[Sales Date]),-12,MONTH))  
Rolling 12 till last 3 month = CALCULATE(sum(Sales[Sales Amount]),DATESINPERIOD('Date'[Date],ENDOFMONTH(dateadd(Sales[Sales Date],-3,month)),-12,MONTH))


Rolling 12 till last month = CALCULATE(sum(Sales[Sales Amount]),DATESINPERIOD('Date'[Date],ENDOFMONTH(dateadd(Sales[Sales Date],-1,month)),-12,MONTH))
Rolling 12  till last 2 month = CALCULATE(sum(Sales[Sales Amount]),DATESINPERIOD('Date'[Date],ENDOFMONTH(dateadd(Sales[Sales Date],-2,month)),-12,MONTH))
Rolling 12 till last 1 month = CALCULATE(sum(Sales[Sales Amount]),DATESINPERIOD('Date'[Date],ENDOFMONTH(dateadd(Sales[Sales Date],-1,month)),-12,MONTH))

To get the best of the time intelligence function. Make sure you have a date calendar and it has been marked as the date in model view. Also, join it with the date column of your fact/s. Refer :
https://radacad.com/creating-calendar-table-in-power-bi-using-dax-functions
https://www.archerpoint.com/blog/Posts/creating-date-table-power-bi
https://www.sqlbi.com/articles/creating-a-simple-date-table-in-dax/

See if my webinar on Time Intelligence can help: https://community.powerbi.com/t5/Webinars-and-Video-Gallery/PowerBI-Time-Intelligence-Calendar-WTD-Y...


Appreciate your Kudos.

[schoden]

Thank you so much ....Really appreciate it 

[Greg_Deckler · Accepted solution]

@schoden - You may find this helpful - https://community.powerbi.com/t5/Community-Blog/To-bleep-With-Time-Intelligence/ba-p/1260000

Also, see if my Time Intelligence the Hard Way provides a different way of accomplishing what you are going for.

https://community.powerbi.com/t5/Quick-Measures-Gallery/Time-Intelligence-quot-The-Hard-Way-quot-TIT...

[schoden]

Thank You so Much 

[nvprasad · Accepted solution]

Hi,

You can get using below Dax 🙂

VAR StartMonth =
    EOMONTH ( TODAY (), -17 ) + 1
VAR EndMonth =
    EOMONTH ( TODAY (), -2 ) + 1
RETURN

CALCULATE ([expression],
    FILTER (
        ALL ( 'Sample'[Invoice Date] ),
        'Sample'[Invoice Date] > StartMonth
            && 'Sample'[Invoice Date] < EndMonth
    ))

Appreciate a Kudos! 🙂
If this helps and resolves the issue, please mark it as a Solution! 🙂

Regards,
N V Durga Prasad

[nthalkar]

M trying to get correct answer for rolling average of last 3 months excluding current month

Rolling Average =

IF(

    ISFILTERED('ZCUSTSALE 01 04 2021 TO LATEST'[MONTH]),

    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('ZCUSTSALE 01 04 2021 TO LATEST'[MONTH].[Date])

    VAR __DATE_PERIOD =

        DATESBETWEEN(

            'ZCUSTSALE 01 04 2021 TO LATEST'[MONTH].[Date],

            STARTOFMONTH(DATEADD(__LAST_DATE, -2, MONTH)),

            __LAST_DATE

        )

    RETURN

        AVERAGEX(

            CALCULATETABLE(

                SUMMARIZE(

                    VALUES('ZCUSTSALE 01 04 2021 TO LATEST'),

                    'ZCUSTSALE 01 04 2021 TO LATEST'[MONTH].[Year],

                    'ZCUSTSALE 01 04 2021 TO LATEST'[MONTH].[QuarterNo],

                    'ZCUSTSALE 01 04 2021 TO LATEST'[MONTH].[Quarter],

                    'ZCUSTSALE 01 04 2021 TO LATEST'[MONTH].[MonthNo],

                    'ZCUSTSALE 01 04 2021 TO LATEST'[MONTH].[Month]

                ),

                __DATE_PERIOD

            ),

            CALCULATE(

                SUM('ZCUSTSALE 01 04 2021 TO LATEST'[ Sale Value]),

                ALL('ZCUSTSALE 01 04 2021 TO LATEST'[MONTH].[Day])

            )

        )

)

as per above dax system will take current month for average calculation .

Please help to correct above dax for correct result

i need rolling average for last 3 month excluding current month. in the month of Oct . Bi should take Sep aug July value for average

[schoden]

@nvprasad  Thank you for the code ...Apprecitate it 😄