Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago
Solved

MTD for Previous period

  If the current MTD is the 1-28th of May, I would like to be able to see the MTD sales for the same period for the 1st-28th of January or any other month.    Greatly appreciate it if someone can ...
  • v-yalanwu-msft's avatar
    v-yalanwu-msft
    4 years ago

    Hi， Anonymous ；

    You could create a measure such as:

    measure = CALCULATE(SUM('Fact'[amount]),FILTER('Table',DAY([Date])>=1&& DAY([Date])<=DAY(TODAY())))

    I tested it, when today is 2022-6-1.so the result is:

    when change the today is 5-28.


    Best Regards,
    Community Support Team _ Yalan Wu
    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

daXtreme's avatar
daXtreme
Icon for Solution Sage rankSolution Sage
4 years ago

 

This does exactly what you want:

[MTD] = 
    // Please note that the Dates table must be
    // marked as a Date Table in the model.
    // MonthOrderNumber must be a unique integer
    // that identifies each month across the whole
    // calendar. So, the very first month in the
    // calendar will have a MonthOrderNumber of 1
    // and then CONSECUTIVELY up until the last month.
    // This number is used to move through months
    // easily.
    // If today is not suitable and you want the
    // day before today, just subtract 1 from
    // today() below and the rest remains the same.
    var Today_ = today()
    var TodaysMonthNumber =
        calculate(
            SELECTEDVALUE( Dates[MonthOrderNumber] ),
            Dates[Date] = Today_,
            ALL( Dates )
        )
    var SelectedMonthNumber = 
        selectedvalue( 'Dates'[MonthOrderNumber] )
    var DifferenceInMonthOrderNumber =
        CurrentlyVisibleMonthNumber - TodaysMonthNumber
    var TodaysMTDPeriod =
        CALCULATETABLE(
            DATESMTD( Dates[Date] ),
            Dates[Date] = Today_
        )
    var RequiredMTDPeriod =
        DATEADD(
            TodaysMTDPeriod,
            DifferenceInMonthOrderNumber,
            MONTH
        )
    var Result =
        CALCULATE(
            [Your Measure],
            RequiredMTDPeriod
        )
    return
        Result

 

  • Anonymous's avatar
    Anonymous
    Not applicable
    4 years ago

    Is it currentlyvisiblemonth number or selectedmonth number - todaymonth number? daXtreme 

    • daXtreme's avatar
      daXtreme
      Icon for Solution Sage rankSolution Sage
      4 years ago

      I gave you a measure, not a calculated column. And the stuff does work. I have checked it in a model I created.

  • Anonymous's avatar
    Anonymous
    Not applicable
    4 years ago

    Hi there, the dax does not seem to work. Please find my DAX below and a picture of my calendar table

    MTD SALES 2 =
    var Today_= TODAY()

    var todaymonthnumber= CALCULATE(SELECTEDVALUE('Calendar'[Month No]),
    'Calendar'[date] = Today_,
    ALL('Calendar')
    )

    var selectedmonthnumber=
    SELECTEDVALUE('Calendar'[Month No])

    var differenceinmonthnumber=
    selectedmonthnumber-todaymonthnumber

    var todaysmtdperiod=
    CALCULATETABLE(
    DATESMTD('Calendar'[date]),
    'Calendar'[date]= Today_
    )

    var requiredmtdperiod=
    DATEADD(
    todaysmtdperiod,
    differenceinmonthnumber,
    MONTH
    )

    var result=
    CALCULATE(
    [Total Sales],
    requiredmtdperiod
    )

    return
    result
     


    daXtreme