Forum Discussion

NPatel12498's avatar
NPatel12498
Frequent Visitor
1 year ago
Solved

Cummulative number month over month

Hi,

 

I wanted to create a meausure that will show the cummulative # of trips month by month for the fiscal year. This is the current chart which shows the # of trips that occurred during each month. I would need to create a measure that shows each number cummulating over each month. 

 

I would also want to add a in a line that may show how much the number changed from the previous month with a plus or minus sign. Example, from oct to november it would be  "-8", from nov to december it would be "+3". The bars would show the cummulative value. 

 

Any help will be appreciated!

 

Need Help
  • Jihwan_Kim's avatar
    Jihwan_Kim
    1 year ago

    Hi,

    I am not sure how your semantic model looks like, but I tried to create a sample pbix file like below.

    Please check the below picture and the attached pbix file.

     

     

     

     

    WINDOW function (DAX) - DAX | Microsoft Learn

     

    # trips cumulate: = 
CALCULATE (
    SUM ( trip[trip] ),
    WINDOW (
        0,
        ABS,
        0,
        REL,
        ALL ( 'calendar'[Year-Month], 'calendar'[Year-Month sort] ),
        ORDERBY ( 'calendar'[Year-Month sort], ASC )
    )
)

     

     

    OFFSET function (DAX) - DAX | Microsoft Learn

     

    diff: = 
VAR _currentmonth =
    SUM ( trip[trip] )
VAR _previousmonth =
    CALCULATE (
        SUM ( trip[trip] ),
        OFFSET (
            -1,
            ALL ( 'calendar'[Year-Month], 'calendar'[Year-Month sort] ),
            ORDERBY ( 'calendar'[Year-Month sort], ASC )
        )
    )
RETURN
    IF ( NOT ISBLANK ( _previousmonth ), _currentmonth - _previousmonth )

     

1 Reply

  • Jihwan_Kim's avatar
    Jihwan_Kim
    Icon for Super User rankSuper User
    1 year ago

    Hi,

    I am not sure how your semantic model looks like, but I tried to create a sample pbix file like below.

    Please check the below picture and the attached pbix file.

     

     

     

     

    WINDOW function (DAX) - DAX | Microsoft Learn

     

    # trips cumulate: = 
CALCULATE (
    SUM ( trip[trip] ),
    WINDOW (
        0,
        ABS,
        0,
        REL,
        ALL ( 'calendar'[Year-Month], 'calendar'[Year-Month sort] ),
        ORDERBY ( 'calendar'[Year-Month sort], ASC )
    )
)

     

     

    OFFSET function (DAX) - DAX | Microsoft Learn

     

    diff: = 
VAR _currentmonth =
    SUM ( trip[trip] )
VAR _previousmonth =
    CALCULATE (
        SUM ( trip[trip] ),
        OFFSET (
            -1,
            ALL ( 'calendar'[Year-Month], 'calendar'[Year-Month sort] ),
            ORDERBY ( 'calendar'[Year-Month sort], ASC )
        )
    )
RETURN
    IF ( NOT ISBLANK ( _previousmonth ), _currentmonth - _previousmonth )