Forum Discussion

achanikya's avatar
achanikya
Icon for Helper I rankHelper I
4 years ago
Solved

Rolling 12 months

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. Pl...
Show and Tell
  • Anonymous's avatar
    Anonymous
    4 years ago

    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's avatar
amitchandak
Icon for Super User rankSuper User
4 years ago

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

Anonymous's avatar
Anonymous
Not applicable
4 years ago

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