Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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!
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?
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.
Your vCurrentMonth is aggregating on the [Fiscal Year] field. Shouldn't it be aggregating on [Fiscal Month]?
I'm sorry I sent you the wrong formula. This is what I have for vCurrentMonth.
Please help
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?