Rolling 12 months

[achanikya]

Hi,

I used the below measure for calculating Rolling 12 Months, but in data it displayed from january-21 to November-21, Actually it should show Dec-20 to Nov-21.

Please refer the below image.

Please find the below measure and advice is anything we have to change.
----------------------------------------------------------------------------------------------------------------------------------------------
Sales amount 12 month =

---------------------------------------------------------

VAR _selectedAltYear =

SELECTEDVALUE( 'To Year'[Year Number])

-----------------------------------------------------

//new date table

VAR __rlvntDateTable =

CALCULATETABLE(

'Date',

FILTER('Date','Date'[Date]>= edate([Max Date of To Year],-12) && 'Date'[Date]<= [Max Date of To Year])

)

---------------------------------------------------------

VAR _altsalesamount =

CALCULATE( [Sales Amount], __rlvntDateTable )

RETURN

_altsalesamount

 -------------------------------------------------------------------------------------------------------------------------------------------

[v-shex-msft · Accepted solution]

Hi @achanikya,

I write a formula to use selection year get the last date from the calendar table and use it as a condition to rolling calculated on the fact table. You can try to use the following measure formula if it is suitable for your requirement:

Sales amount 12 month =
//get the selection year from new date table 
VAR sYear =
    MAX ( 'To Year'[Year] ) //get the last date with selected year from calendar table
VAR selected =
    CALCULATE (
        MAX ( 'Date'[Date] ),
        FILTER ( ALLSELECTED ( 'Date' ), YEAR ( [Date] ) = sYear )
    )
RETURN
    //rolling calculation
    CALCULATE (
        [Sales Amount],
        FILTER (
            ALLSELECTED ( 'Fact' ),
            //replace this wiht your fact table
            [Date]
                >= DATE ( YEAR ( selected ) - 1, MONTH ( selected ), DAY ( selected ) )
                && [Date] <= selected
        ),
        VALUES ( 'Fact'[System Name] )
    )

If the above does not help, can you please share a pbix or some dummy data that keep raw data structure with expected results? They should help us clarify your scenario and test to coding formula.

How to Get Your Question Answered Quickly  

Regards,

Xiaoxin Sheng

[amitchandak]

@achanikya , Try like,

Sales amount 12 month =
---------------------------------------------------------
VAR _selectedAltYear =
maxx(allselected('Date'), 'Date'[Date]<=)
-----------------------------------------------------
//new date table
VAR __rlvntDateTable =
CALCULATETABLE(
'Date',
FILTER('Date','Date'[Date]>= eomonth(_selectedAltYear,-12) && 'Date'[Date]<= _selectedAltYear)
)
---------------------------------------------------------
VAR _altsalesamount =
CALCULATE( [Sales Amount], __rlvntDateTable )
RETURN
_altsalesamount

But if you are selecting a date and then want 12 month axis, you need independent table

Need of an Independent Date Table:https://www.youtube.com/watch?v=44fGGmg9fHI

[v-shex-msft]

Hi @achanikya,

Any update for these? Did the above suggestions help with your scenario? if that is the case, you can consider Kudo or accept the helpful suggestions to help others who faced similar requirements.

If these also don't help, please share more detailed information to help us clarify your scenario to test.

How to Get Your Question Answered Quickly 

Regards,

Xiaoxin Sheng