Counting current month totals in Set Analysis Expression
Hi Community.
I have planned sales data in a monthly line chart from Jan to Dec.
I have the following expression and what I am trying to accomplish is to have a data point that shows the monthly total and changes as we go from month to month:
I will have to check tomorrow to see if the month will change to December, but thus far I am able to show as intended:
Dual('Current Month, '& Count({$<[Date Type]={'VPP Dates'},Reference_Only={'No'},[Month of Year Abbreviated Name]={$(=Date(Today(),'MMM'))}>}Gateways), Count({$<[Date Type]={'VPP Dates'},Reference_Only={'No'},[Month of Year Abbreviated Name]={$(=Date(Today(),'MMM'))}>}Gateways) )
Date={$(=Date(Today(),'MMMM'))} will not work. You either have to create a master calendar and do Set analysis against month field in the master calendar. Or you will need to do something like below Date={">=$(=MonthStart(Today()))"} , Date={"<=$(=Today())"}
Typing on my phone so may not perfect but you get the idea
Previous one you have to consider date format too I believe. But If you have a master calendar use set analysis on that along with month function E.g. month={"$(=month(today ())"}
LOAD %Date, Date, Year, [Quarter Number], [Quarter Name], [Month of Year], [Month of Year Name], [Month of Year Abbreviated Name], [Week of Year], [Day of Week], [Day of Week Name], [Day of Week Abbreviated Name], [Day of Month], [Day of Year], [Days in Month], Date(MonthStart(Date),'MMM YY') as YearMonth, [Leap Year Flag], [Weekend Flag], [Production Month], [Production Year], [Issue Number], [Julian Date], [Week Ending Friday]
I tried using Month of Year:
=Date(Today(),'MMM')& ', '& Count({$<[Date Type]={'VPP Dates'},Reference_Only={'No'},[Month of Year]={"$(=[Month of Year](today ())"}>}Gateways)
All I want to show is this months distribution and not every other month (YTD), currently the graph is showing Jan through Dec as the expression is a count of total planned for each month.
