Forum Discussion

nshindel's avatar
nshindel
Frequent Visitor
4 years ago
Solved

Previous Moth to Date for same dates as current month

I am looking to create a measure based that looks at a total from the previous month, based on the current date.

 

So, if today is June 17th, I would like the measure to show May 1st-16th. I have tried a few solutions, but nothing as worked.

 

Attribute MTD Previous = CALCULATE(SUM('table'[Value]),PREVIOUSMONTH(DATESMTD('table'[Date])))
 
Attribute MTD Previous2 = CALCULATE(SUM('table'[Value]),DATESMTD(dateadd(DateTable[Date],-1,MONTH)))
 
Attribute MTD Previous3 = CALCULATE(SUM('table'[Value]),DATESMTD(dateadd(table[Date],-1,MONTH)))

Attribute MTD Previous4 = CALCULATE(SUM('table'[Value]),dateadd(table[Date],-1,MONTH))

 
 

 

 

Need Help
  • nshindel's avatar
    nshindel
    4 years ago

    Figured this out for anyone interested

    Attribute MTD Previous Month = 
var yesterday = TODAY()-1

var currentmonth = MONTH(yesterday)
var currentyear = YEAR(yesterday)
var currentday = DAY(yesterday)

var priormonth = if(currentmonth = 1,12, currentmonth - 1)
var totalvalue = sum('Adobe - Master Overall Pivot'[Value])

RETURN CALCULATE(SUM('table'[Value]),DateTable[DayOfMonth] <= currentday,DateTable[MonthNumberOfYear]=priormonth,DateTable[Year]=currentyear)

     

2 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    4 years ago

    Hi nshindel ,

     

    Please try:

     

    Measure = 
var _preMonth= EOMONTH(TODAY(),-1) // 2022.5.31
var _start= EOMONTH(TODAY(),-2)+1  // 2022.4.30+1=2022.5.1
return  CALCULATE(SUM('Table'[Value]),FILTER('Table',[Date]>=_start && [Date]< DATE(YEAR(_preMonth),MONTH(_preMonth),DAY(TODAY()))))

     

    On my side ,today is 2022/6/20, I have created a test measure to check if the method works:

     

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

  • nshindel's avatar
    nshindel
    Frequent Visitor
    4 years ago

    Figured this out for anyone interested

    Attribute MTD Previous Month = 
var yesterday = TODAY()-1

var currentmonth = MONTH(yesterday)
var currentyear = YEAR(yesterday)
var currentday = DAY(yesterday)

var priormonth = if(currentmonth = 1,12, currentmonth - 1)
var totalvalue = sum('Adobe - Master Overall Pivot'[Value])

RETURN CALCULATE(SUM('table'[Value]),DateTable[DayOfMonth] <= currentday,DateTable[MonthNumberOfYear]=priormonth,DateTable[Year]=currentyear)