Forum Discussion

basketballer202's avatar
basketballer202
Frequent Visitor
4 years ago
Solved

MTD function based of week num

Hi Team,   Looking for a little guidance.  I'd like to calculate MTD revenue using week numbers that are tied to a month   So for example:   Month 1 = week 1/2/3/4   Month 2 = week 5/6/7/8/9 ...
  • amitchandak's avatar
    amitchandak
    4 years ago

    basketballer202 , if week and month are part of date table , you can get it using time intelligence

     

    MTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESMTD('Date'[Date]))

     

    MTD =
    var _max = if(isfiltered('Date'),MAX( 'Date'[Date]) , today())
    var _min = eomonth(_max,-1)+1 ,
    return
    CALCULATE([net] ,DATESBETWEEN('Date'[Date],_min,_max))

     

     

amitchandak's avatar
amitchandak
Icon for Super User rankSuper User
4 years ago

basketballer202 , if week and month are part of date table , you can get it using time intelligence

 

MTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESMTD('Date'[Date]))

 

MTD =
var _max = if(isfiltered('Date'),MAX( 'Date'[Date]) , today())
var _min = eomonth(_max,-1)+1 ,
return
CALCULATE([net] ,DATESBETWEEN('Date'[Date],_min,_max))