topic Re: Current Month Vs Last Year Same Month Sales in Visualization and Usability

Current Month Vs Last Year Same Month Sales

Isgranda — Sat, 20 Jun 2020 00:46:58 GMT

Hello -

I have a filter pane where the user selects the month and I want my bar graph to show the month selected sales and the same month from last year sales. Ex: If the user selects May 2020 I want the bar graph to show the net sales for May 2020 and May 2019. If the user selects May 2019 I want the bar graph to show the May 2019 and May 2018 net sales and so on and so forth.

I created a variable called vMonthago12 = =DATE(addmonths(vCurrentMonth,-12),'MMM/YYYY')

and this is the formula I'm currently using:

=SUM({<[Calendar Month]={"<=$(vMonthAgo12)"}>}[Daily Net Sales])

and this is the result I'm getting:

Any help is appreciated - Thank you!

Re: Current Month Vs Last Year Same Month Sales

fosuzuki — Sat, 20 Jun 2020 10:27:04 GMT

Hi,

what do you have in vCurrentMonth?

Also, if you want only one month, I think that you should remove the "less than" signal from the set analysis, right?

Re: Current Month Vs Last Year Same Month Sales

Isgranda — Mon, 22 Jun 2020 20:11:22 GMT

Hello, Thanks for replying.

vCurrentMonth = maxstring({1<[Calendar Year/Month]={'$(vCurrentMonth)'}>}[Fiscal Year])

I'm including the "less than" in my set analysis because, if the user filters on for example: March, April, and May 2020. I want the bar graph to display the values for March, April, and May 2019 (previous year). If the user selects March 2020, I want the bar graph to display the values for March 2019.

Basically, I want a dynamic output.

Re: Current Month Vs Last Year Same Month Sales

fosuzuki — Tue, 23 Jun 2020 07:31:57 GMT

Your vCurrentMonth is aggregating on the [Fiscal Year] field. Shouldn't it be aggregating on [Fiscal Month]?

Re: Current Month Vs Last Year Same Month Sales

Isgranda — Thu, 25 Jun 2020 16:25:12 GMT

I'm sorry I sent you the wrong formula. This is what I have for vCurrentMonth.

Re: Current Month Vs Last Year Same Month Sales

Isgranda — Thu, 23 Jul 2020 21:32:47 GMT

Please help

Re: Current Month Vs Last Year Same Month Sales

fosuzuki — Tue, 28 Jul 2020 17:57:08 GMT

Two questions:

1. Put vMonthAgo12 in a textbox with the expression ='$(vMonthAgo12)'. What values does it show?

2. How you are creating the field Calendar Month? Can you put the script code?