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

     

     

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

Hi,

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

Please check the below picture and the attached pbix file.

I hope this provides some idea on how to create a solution for your data model.

 

 

 

Sales: = 
SUM( Sales[Revenue] )

 

 

 

Sales MTD: =
IF (
    HASONEVALUE ( 'Calendar'[Month] ),
    CALCULATE (
        [Sales:],
        FILTER (
            ALL ( 'Calendar' ),
            'Calendar'[Month] = MAX ( 'Calendar'[Month] )
                && 'Calendar'[Week] <= MAX ( 'Calendar'[Week] )
        )
    )
)

 

 

In case if year column matters, please also put the condition that looks for the same year as well.