Set Expression evaluating current month in bar chart shows full set of possible months - just with 0 value for months not in scope

[eldaniello]

Hi,

I want to create a measure that will evaluate the current month and based on that display different sets of data on the bar chart. 

If it's January, it should show full previous year.

If its not January, it should show current year until the last day of the previous month (report is generated monthly on 3rd day of the month).

I wrote below expression for received items but it is not working properly. It displays all possible months for both options of an "if". It shows full previous year and all finished current year's months, only puts zeroes in the months that are not in the scope. 

I don't want to use "hide zero values" option as sometimes "0" is ok on the chart and hiding it will disrupt the information of the chart. 

Can you please help me figuring out the way to design expression so that the time scope will be correctly displayed?

Thanks!

Expression:

if(Num(Month(Today()))=1,
count({<DateType={'Received'}, REQUEST_DATE.Date={">=$(=YearStart(Max(Today()),-1))<$(=MonthStart(Max(Today())))"}>} distinct(ISSUE_ID)),
count({<DateType={'Received'}, REQUEST_DATE.Date={">=$(=YearStart(Max(Today())))<$(=MonthStart(Max(Today())))"}>} distinct(ISSUE_ID))
)

I also tried this:

if(Num(Month(Today()))=1,
count({<DateType={'Received'}, REQUEST_DATE.Date={">=$(=YearStart(Max(Today()),-1))<$(=MonthStart(Max(Today())))"}>} distinct(ISSUE_ID)),
if(Num(Month(Today()))>1,count({<DateType={'Received'}, REQUEST_DATE.Date={">=$(=YearStart(Max(Today())))<$(=MonthStart(Max(Today())))"}>} distinct(ISSUE_ID)),null())
)

The chart should show only Jan-2024 but this is how it is displayed now:

[marcus_sommer]

You may try:

count({<DateType={'Received'},
REQUEST_DATE.Date={">=$(=YearStart(Today(),(Month(Today())=1)))<$(=MonthStart(Today()))"}>} distinct(ISSUE_ID)

[eldaniello · Accepted solution]

Hi Marcus,

thanks for taking interest in the topic - I actually found a solution in the meantime.
I decided to use variable to do the evaluation of the month and embed it in the expression of the chart.

variable vDateScope:
=if(Num(Month(Today()))=1,'">=$(=YearStart(Max(Today()),-1))<$(=MonthStart(Max(Today())))"','">=$(=YearStart(Max(Today())))<$(=MonthStart(Max(Today())))"')

modified chart measure expression:
count({<DateType={'Received'}, REQUEST_DATE.Date={$(=(vDateScope))}>} distinct(ISSUE_ID))