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

     

     

Anonymous's avatar
Anonymous
Not applicable
4 years ago

Hi  basketballer202 ,

I created some data:

Here are the steps you can follow：

1. Create measure.

Flag =
var _select=SELECTEDVALUE('Slicer'[Week])
var _selectmonth=CALCULATE(MAX('Table'[Month]),FILTER(ALL('Table'),'Table'[Week]=_select))
return
IF(
    MAX('Table'[Month])=_selectmonth&&MAX('Table'[Week])<=_select,1,0)

Measure =
SUMX(ALLSELECTED('Table'),[Amount])

2. Place [Flag]in Filters, set is=1, apply filter.

3. Result:

 

If you need pbix, please click here.

 

Best Regards,

Liu Yang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly