Need help with your date calculations? Schedule an hour with Eric here: www.eventbrite.com/e/tableau-office-hours-with-eric-parker-tickets-42691995909 Ready to master Tableau? Check out our lineup of upcoming classes! www.eventbrite.com/o/onenumber-15678294163
My gd... this is by far the most straightforward and sensible way to calculate between the current and previous month-to-date calculation!! Thank you for this!
Thank you! It should be possible but how I would approach it depends on the situation. What you do want to filter? The "current" month? Or the "previous" month? Or both? In that case it might not be a "month to date" comparison but more of a month over month comparison?
@@onenumbertableau Thanks for the answer, but now I understand how it was done and it really works by filtering the month. My idea would be to do MTD in the same field and compare internal months. In my case, I had to create a box for MTD and another comparing closed months, the idea would be just one.
@@georgedossantosaraujo2611 I suppose one option is to replace the 1 in the DATEDIFF function with a parameter. With an integer parameter you could hook it up to 1 is labeled as Last Month, 2 is 2 Months Ago, 3 is 3 Months Ago, etc. It's a little clunky though because you aren't just selecting "December 2023" as the comparison option. I think the best approach would be: 1. Calculate a Field for Month and Year Number (e.g. 2023-12) which doesn't include the current month 2. Auto-populate a parameter with those values 3. Default the parameter to selecting the latest value (i.e. last month) 4. Write a calculation that only pulls data from the selected month in the parameter Here's some background on dynamic parameters: onenumber.biz/blog-1/2020/3/11/how-to-make-dynamic-parameters-in-tableau-new-feature Feel free to check out our office hours if you want help setting something like this up: www.eventbrite.com/e/tableau-expert-office-hours-tickets-42691995909
Hi Martin, is it possible to apply the aggregation after the month to date logic? For instance, let's say you want to calculate month to date headcount. COUNTD(IF DATEDIFF('month',[Date],TODAY()) = 0 THEN [Employee ID] END). More on this concept here: onenumber.biz/blog-1/2018/1/30/tableau-cannot-mix-aggregate-and-non-aggregate-comparisons-or-results-in-if-expressions
@@martinswiac2647 I don't know for sure but I have a feeling the ATTR function won't fix this. I suppose you could try the FIXED curly braces so Tableau doesn't read the aggregated value as aggregated. More info on that here: onenumber.biz/blog-1/2018/4/23/tableau-cannot-mix-aggregate-and-non-aggregate-arguments-with-this-function If those options don't work, feel free to shoot me an email (address at end of video) with some screenshots of the fields and calculations and I can try to help you address it there.
@@FPrimeHD1618 Yes! We ended up using a calculation like this: SUM(IF DATEDIFF('month',[Date],TODAY()) = 0 THEN [Acceptance Value] END) / SUM(IF DATEDIFF('month',[Date],TODAY()) = 0 THEN [Denominator Value] END)
Hi, OneNumber LLC I'm trying to do the same calculation to compare weeks and it doesn't work right. Can you have a look and tell where the problem is in my calculations below? Date DATEADD('year',1,[Date Sale (Kiev)]) Price, USD PWTD IF DATEDIFF('week',[Date],TODAY()) = 1 AND WEEK([Date])
@@onenumbertableau thank you very much, Eric! I've been looking for this calculation for some time now. You even got the percent difference calculated as an example. Sweet!
Hey Matt, so are you more interested in comparing the percentage of the month that's passed than total days? For instance, the 27th of February is ~96% of the way through February vs. the 30th of January is about 97% through January so 27 days of February is roughly equivalent to 30 days of January?
Hi! Funny enough we did a blog post on this a little while back here: onenumber.biz/blog-1/2021/6/9/month-to-date-sales-compared-to-last-12-months-in-tableau
This is great, thanks for sharing!!But, the formula doesn’t work for January of a new year and December of last year. Is there a work around to compare January of current year against December of last year?
Oh I added one more condition which is year(date) = year(today()) when I created the dashboard. I removed that line of formula and it’s working fine now. Thanks again!!!
@@yon6729 I'm glad to hear it's working! So DATEDIFF('month',[Date],TODAY()) = 0 means the value is from this month (currently Jan 2023) only! DATEDIFF('month',[Date],TODAY()) = 12 would be Jan 2022.
Great video! I'm working with live data and when I used your formula I'm getting the MTD showing 7 days (Today is Sept 8) and my PMTD is showing 8 days. Is there a fix for this?
Hi Thinh, I think you'll find this downloadable workbook helpful: public.tableau.com/app/profile/eric.parker/viz/MasteringTableauDateCalculations-WebinarAccompanyingWorkbook/MasteringTableauDateCalculationsHome
Hi Jesse, you can create a parameter and replace all the references to the "TODAY()" function to that parameter. You can even make that date parameter dynamic. More on parameter creation here: onenumber.biz/blog-1/2020/3/11/how-to-make-dynamic-parameters-in-tableau-new-feature
![โฮ่ง! (SUGOI) - LYKN [ OFFICIAL MV ]](http://i.ytimg.com/vi/nFp4zjAARFs/mqdefault.jpg)
Need help with your date calculations? Schedule an hour with Eric here: www.eventbrite.com/e/tableau-office-hours-with-eric-parker-tickets-42691995909
Ready to master Tableau? Check out our lineup of upcoming classes! www.eventbrite.com/o/onenumber-15678294163
My gd... this is by far the most straightforward and sensible way to calculate between the current and previous month-to-date calculation!! Thank you for this!
I'm so glad you found this helpful. Thanks for letting us know!
Your content is so touching
I'm so glad it was helpful!
Great lesson! Thank you)
You're welcome!
very practical for my current concerns, thank you for your sharing
So glad to hear it's what you needed!
Very helpful, thanks a lot!
Glad to hear it!
Great video! How I can compare this month datetime with previous month datetime? I compare my metric every hour?
I think the Prior Month Calculation would be...
IF DATEDIFF('month',[Date],TODAY() = 1
AND [Date]
Thank you.
Glad to help!
Great video!!...
One question is it possible to put a month filter?
Thank you! It should be possible but how I would approach it depends on the situation. What you do want to filter? The "current" month? Or the "previous" month? Or both? In that case it might not be a "month to date" comparison but more of a month over month comparison?
@@onenumbertableau Thanks for the answer, but now I understand how it was done and it really works by filtering the month. My idea would be to do MTD in the same field and compare internal months. In my case, I had to create a box for MTD and another comparing closed months, the idea would be just one.
@@georgedossantosaraujo2611 I suppose one option is to replace the 1 in the DATEDIFF function with a parameter. With an integer parameter you could hook it up to 1 is labeled as Last Month, 2 is 2 Months Ago, 3 is 3 Months Ago, etc. It's a little clunky though because you aren't just selecting "December 2023" as the comparison option.
I think the best approach would be:
1. Calculate a Field for Month and Year Number (e.g. 2023-12) which doesn't include the current month
2. Auto-populate a parameter with those values
3. Default the parameter to selecting the latest value (i.e. last month)
4. Write a calculation that only pulls data from the selected month in the parameter
Here's some background on dynamic parameters: onenumber.biz/blog-1/2020/3/11/how-to-make-dynamic-parameters-in-tableau-new-feature
Feel free to check out our office hours if you want help setting something like this up: www.eventbrite.com/e/tableau-expert-office-hours-tickets-42691995909
Hi, great video! How would you proceed if you have aggregate variable instead of Sales. Thanks!
Hi Martin, is it possible to apply the aggregation after the month to date logic? For instance, let's say you want to calculate month to date headcount. COUNTD(IF DATEDIFF('month',[Date],TODAY()) = 0 THEN [Employee ID] END). More on this concept here: onenumber.biz/blog-1/2018/1/30/tableau-cannot-mix-aggregate-and-non-aggregate-comparisons-or-results-in-if-expressions
@@onenumbertableau Can't do that since my variable has to be aggregated. I was thinking about using ATTR() for the date function?
@@martinswiac2647 I don't know for sure but I have a feeling the ATTR function won't fix this. I suppose you could try the FIXED curly braces so Tableau doesn't read the aggregated value as aggregated. More info on that here: onenumber.biz/blog-1/2018/4/23/tableau-cannot-mix-aggregate-and-non-aggregate-arguments-with-this-function If those options don't work, feel free to shoot me an email (address at end of video) with some screenshots of the fields and calculations and I can try to help you address it there.
You ever figure this out?
@@FPrimeHD1618 Yes! We ended up using a calculation like this:
SUM(IF DATEDIFF('month',[Date],TODAY()) = 0 THEN [Acceptance Value] END)
/
SUM(IF DATEDIFF('month',[Date],TODAY()) = 0 THEN [Denominator Value] END)
Where is March 1, 2021?
I think there were no sales that day and as a result no rows of data for that date!
Hi, OneNumber LLC
I'm trying to do the same calculation to compare weeks and it doesn't work right.
Can you have a look and tell where the problem is in my calculations below?
Date
DATEADD('year',1,[Date Sale (Kiev)])
Price, USD PWTD
IF DATEDIFF('week',[Date],TODAY()) = 1
AND WEEK([Date])
Hi Sergey, does this help? onenumber.biz/blog-1/2020/9/7/week-to-date-vs-previous-week-to-date-in-tableau-wtd
@@onenumbertableau thank you very much, Eric! I've been looking for this calculation for some time now. You even got the percent difference calculated as an example. Sweet!
How would you account for comparing February (28 total days) vs. January (31 total days)?
Hey Matt, so are you more interested in comparing the percentage of the month that's passed than total days? For instance, the 27th of February is ~96% of the way through February vs. the 30th of January is about 97% through January so 27 days of February is roughly equivalent to 30 days of January?
How could you do previous month to date for each month for a rolling 13 months instead of just prior month?
Hi! Funny enough we did a blog post on this a little while back here: onenumber.biz/blog-1/2021/6/9/month-to-date-sales-compared-to-last-12-months-in-tableau
This is great, thanks for sharing!!But, the formula doesn’t work for January of a new year and December of last year. Is there a work around to compare January of current year against December of last year?
Hi Yon! That's odd. It should work across years. Can you paste your formulas in a reply so I can figure out why that's happening for you?
Oh I added one more condition which is
year(date) = year(today()) when I created the dashboard. I removed that line of formula and it’s working fine now. Thanks again!!!
@@yon6729 I'm glad to hear it's working! So DATEDIFF('month',[Date],TODAY()) = 0 means the value is from this month (currently Jan 2023) only! DATEDIFF('month',[Date],TODAY()) = 12 would be Jan 2022.
Yes, that’s right! My formula for current MTD is ok. But, PMTD was
sum(if datediff(‘month’, [Date], Today()) = 1 AND DAY([Date])
Great video! I'm working with live data and when I used your formula I'm getting the MTD showing 7 days (Today is Sept 8) and my PMTD is showing 8 days. Is there a fix for this?
Interesting. Are there any missing dates in your data set for this month?
how did you do it can you share with me , thank you
Hi Thinh, I think you'll find this downloadable workbook helpful: public.tableau.com/app/profile/eric.parker/viz/MasteringTableauDateCalculations-WebinarAccompanyingWorkbook/MasteringTableauDateCalculationsHome
how can i create a calculation such that if i choose a date (month) from the orderdate that it gives me the previous month sales dynamically
Hi Jesse, you can create a parameter and replace all the references to the "TODAY()" function to that parameter. You can even make that date parameter dynamic. More on parameter creation here: onenumber.biz/blog-1/2020/3/11/how-to-make-dynamic-parameters-in-tableau-new-feature