Forum Discussion

yalla's avatar
yalla
New Member
5 years ago
Solved

last month to date based on current date

If today is November 4 i want to calculate some measures as below requirement 

1. sum(sales) between October 1 to October 4 (i.e last 1 month same period)

 

i tried this measure but giving the result from Oct 1 to Oct 3 but  i want from oct 1 to oct 4th.

 

1-MonthSamePeriod =
CALCULATE (
    SUM ( 'Table'[Sales] ),
    FILTER (
        'Table',
        AND (
            [Date]
                >= DATE ( YEAR ( TODAY () ), MONTH ( TODAY () ) - 1, 1 ),
            [Date]
                <= DATE ( YEAR ( TODAY () ), MONTH ( TODAY () ) - 1, DAY ( TODAY () ) - 1 )
        )
    )
)
Need Help
  • v-yingjl's avatar
    v-yingjl
    5 years ago

    Hi yalla ,

    Since you want to caulate 10-1 to 10-4 in this case based on 11-4, why did you use -1 in Day()?

    Just delete -1 in the formula and it should work.

    1-MonthSamePeriod = 
CALCULATE (
    SUM ( 'Table'[Sales] ),
    FILTER (
        'Table',
        AND (
            [Date]
                >= DATE ( YEAR ( TODAY () ), MONTH ( TODAY () ) - 1, 1 ),
            [Date]
                <= DATE ( YEAR ( TODAY () ), MONTH ( TODAY () ) - 1, DAY ( TODAY () )  )
        )
    )
)

     

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

     

     

3 Replies

  • amitchandak's avatar
    amitchandak
    Icon for Super User rankSuper User
    5 years ago

    yalla , Try like examples

    Last MTD QTY forced=
    var _max = date(year(today()),month(today())-1,day(today()))
    return
    CALCULATE(Sum('order'[Qty]),DATESMTD(dateadd('Date'[Date],-1,year)),'Date'[Date]<=_max)

     

    or

     

    Last MTD QTY forced=
    var _max = date(year(today()),month(today())-1,day(today()))
    return
    if('Date'[Date]<=_max , CALCULATE(Sum('order'[Qty]),DATESMTD(dateadd('Date'[Date],-1,year))), blank())

    • yalla's avatar
      yalla
      New Member
      5 years ago

      in if condition is not able to pick date column.do we have other measure

       

      • v-yingjl's avatar
        v-yingjl
        Icon for Community Support rankCommunity Support
        5 years ago

        Hi yalla ,

        Since you want to caulate 10-1 to 10-4 in this case based on 11-4, why did you use -1 in Day()?

        Just delete -1 in the formula and it should work.

        1-MonthSamePeriod = 
CALCULATE (
    SUM ( 'Table'[Sales] ),
    FILTER (
        'Table',
        AND (
            [Date]
                >= DATE ( YEAR ( TODAY () ), MONTH ( TODAY () ) - 1, 1 ),
            [Date]
                <= DATE ( YEAR ( TODAY () ), MONTH ( TODAY () ) - 1, DAY ( TODAY () )  )
        )
    )
)

         

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