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.
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
 
 -------------------------------------------------------------------------------------------------------------------------------------------
 

 

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

3 Replies

  • 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

  • Anonymous's avatar
    Anonymous
    Not applicable
    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