12 Replies Latest reply on Aug 14, 2019 10:53 AM by Soumitra Godbole

# Help to get this logic for date calculation

Hi,

I am trying to replicate this logic.

Current sales is SUM(if MONTH([Orderdate])=8 then [Sales]end)

Here, i hard coded value 8 which is august month

Prior month is SUM(if MONTH([Orderdate])=7 then [Sales]end)

here, i hard coded value 7 which is july month

Instead of hard coding the value , i need to get the logic based on month i selected. Example in the month field (order date) if i select july 2019 it should show the current month sales for july 2019 and previous month sales for June 2019.

I need three calculated fields, 1. current month sales, 2. prior month sales, 3. sales change (current sales - prior sales)

Kindly help.

• ###### 1. Re: Help to get this logic for date calculation

Hi Daniel,

The simplest approach would be to create a Date Parameter and then use it in the above formulas instead of 7 and 8 as mentioned. This will give you your desired result. For the Parameter you can get the values or populate it using the Order Date field. Here is a screenshot and I have enclosed a packaged workbook.

Hope this answers your question.

Best wishes !

Sincerely,

Soumitra

1 of 1 people found this helpful
• ###### 2. Re: Help to get this logic for date calculation

You mentioned two different things.  First was "selecting" a month.

But you also mention "current" month.

We can make this perpetually dynamic either way, but there are different approaches depending on what you actually want to do.

1 of 1 people found this helpful
• ###### 3. Re: Help to get this logic for date calculation

It throws me an error. cant compare integer and date value. how to approach?

1 of 1 people found this helpful
• ###### 4. Re: Help to get this logic for date calculation

Hi Joe,

what i trying to achieve is , if i select any month in the month field it should show me the previous month sales and current month sales.

lets assume, i click july 2019 in order date, it should show current month sales for july 2019 and previous month sales for june as two calculated fields

1 of 1 people found this helpful
• ###### 5. Re: Help to get this logic for date calculation

To add to what Soumitra said, your parameter can be as simple as 12 rows (Jan/Feb/etc.)  Or it might be as many months as you have in your data:  Jan2016,Feb2016, ... June2019,July2019,Aug2019...

And if you are always basing it off the most current month, we have a function TODAY() that gives you today's date, and you can pull the month from that.  Or you can grab the last date in your data and pull the month off that:

{ FIXED : MAX([Order Date]) }

So that's why I asked what you are really looking to do.

1 of 1 people found this helpful
• ###### 6. Re: Help to get this logic for date calculation

See attached:

1 of 1 people found this helpful
• ###### 7. Re: Help to get this logic for date calculation

Selected month is displayed in the title.  Now you can use that calc (or that calc minus 1) for your calcs in place of the hard-coded values.

1 of 1 people found this helpful
• ###### 8. Re: Help to get this logic for date calculation

Hello again,

Sorry I had issues uploading the attachment and screenshot image earlier and just fixed that. I also appreciate Joe Oppelt's detailed solution with enhancements.

Here are the calculation steps from my example

1) First we create a Date Truncated by Month (In case you have multiple days when the sales are made)

2) Then we Create a Prior Month (also Truncated) Calculation

3) Current Sales

4) Prior Month Sales

5) Difference from Prev Month (MoM)

Then with a little creativity, you can add a little color (Red /Green for -/+ values) and a Header that changes with the Parameter Month value selected.

Also note that since the data is From May-2019 till Aug-2019, if you select May-2019, The Previous Month value will be Null (Blank) along with the Difference (MoM).

Sincerely,

Soumitra

1 of 1 people found this helpful
• ###### 9. Re: Help to get this logic for date calculation

Hi Daniel,

Did this solve your problem. If yes, then please mark as helpful / correct answer to close the thread. Thanks !

Sincerely,

Soumitra

1 of 1 people found this helpful
• ###### 10. Re: Help to get this logic for date calculation

Hi Joe and Soumitra,

Thanks for reply. It works for current year and month. if i click the parameter for Jan 2019. it only showing for Jan 2019 values but not prior month which is dec 2018.

This is my logic for Current Month :

COUNTD(if MONTH([Order Date])= MONTH([Parameter])

and YEAR([Order Date]])=YEAR([Parameter])

then [number] end)

But for Prior month calculation:

COUNTD(if MONTH([Order Date)= MONTH([Parameter])-1

and YEAR([Order Date)=YEAR([Parameter])

then [number] end)

Prior calculation working fine for current year.. lets say if i click the parameter for Jan 2019. current sales showing but prior month not showing.

any idea how to change the logic for prior month calculation to show when i click jan2019 in parameter , it should show Dec 2018

1 of 1 people found this helpful
• ###### 11. Re: Help to get this logic for date calculation

Hi SOUMITRA

it perfectly works. thankyou

1 of 1 people found this helpful
• ###### 12. Re: Help to get this logic for date calculation

Hi Daniel,

You're always welcome ! I'm glad to be of help. The Month over Month analysis has been a very popular and frequently used calculation in most businesses around the globe for many years, so it did not take long to figure out how to go about.

One thing to remember with Parameters is every time you refresh the data-source to get data for the next month, make sure you add that extra month in the Date Parameter as it is not yet Dynamic (needs to be updated manually).

Hopefully, the Dynamic Parameter will be coming soon in a new release. Best wishes !

Sincerely,

Soumitra

1 of 1 people found this helpful