cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Kpower
Frequent Visitor

Previous MTD

Hello,

 

the calculation for Prev MTD is like this

Last MTD = CALCULATE(sum(DAILYTRANSACTIONS[Amount]), DATEADD(DATESMTD(YEAR_MONTH_DIMENSION_TAB[Date]), -1, MONTH))

 

The value is showing if only I selected month filter as Feb 2017.

 

And for this

Prev MTD = CALCULATE(DAILYTRANSACTIONS[Amount MTD], SAMEPERIODLASTYEAR(YEAR_MONTH_DIMENSION_TAB[Date]))

It shows Dec 2016 value instead of Jan 2017.

 

Any help would be appreciated.

 

Thanks

 

1 ACCEPTED SOLUTION
v-shex-msft
Community Support
Community Support

Hi @Kpower,

 

You can refer to below formula to calculate the running total and the previous running total.

 

Measures:

 

 

RunningTotal = CALCULATE(SUM('Table'[Amount]),FILTER(ALL('Table'),'Table'[Date]<=MAX('Table'[Date])))

PreviousRunningTotal = 
var temp=MAX('Table'[Date])
Return
CALCULATE(SUM('Table'[Amount]),FILTER(ALL('Table'),'Table'[Date]<=DATE(YEAR(temp),MONTH(temp)-1,DAY(temp))))

 

 

 

Regards,

Xiaoxin Sheng

Community Support Team _ Xiaoxin
If this post helps, please consider accept as solution to help other members find it more quickly.

View solution in original post

1 REPLY 1
v-shex-msft
Community Support
Community Support

Hi @Kpower,

 

You can refer to below formula to calculate the running total and the previous running total.

 

Measures:

 

 

RunningTotal = CALCULATE(SUM('Table'[Amount]),FILTER(ALL('Table'),'Table'[Date]<=MAX('Table'[Date])))

PreviousRunningTotal = 
var temp=MAX('Table'[Date])
Return
CALCULATE(SUM('Table'[Amount]),FILTER(ALL('Table'),'Table'[Date]<=DATE(YEAR(temp),MONTH(temp)-1,DAY(temp))))

 

 

 

Regards,

Xiaoxin Sheng

Community Support Team _ Xiaoxin
If this post helps, please consider accept as solution to help other members find it more quickly.

View solution in original post

Helpful resources

Announcements
PBI_User Group Leader_768x460.jpg

Manage your user group events

Check out the News & Announcements to learn more.

Welcome Super Users.jpg

Super User Season 2

Congratulations, the new Super User Season 2 for 2021 has started!

Community Connections 768x460.jpg

Community & How To Videos

Check out the new Power Platform Community Connections gallery!

Teds Dev Camp Oct. 2021 768x460.jpg

Power BI Dev Camp - October 28th, 2021

Mark your calendars and join us for our next Power BI Dev Camp!