set zero value as null in table

[alanwong1178]

I created a table show sales  in million for 2021 and 2020.

First column is dimension:  [SALES_DATE.autoCalendar.Month].

Second column is set analysis : 
Sum({<[SALES_DATE.autoCalendar.Year] = {'2021'}>}SALES)/1000000

Third column is set analysis :

Sum({<[SALES_DATE.autoCalendar.Year] = {'2020'}>}SALES)/1000000

May I know how can I present data as below for 2021 column. As I have imported data until Jun21 , I would like show all the data after Jun 21 as null to avoid misunderstanding to user that sales for JUL21 is 0

Please consider the fact that I have a filter [SALES_DATE.autoCalendar.YearMonth] , I would like to ignore this filter and keep all the value for future month as null

[stevejoyce]

Start the expression with an if-statement condition.

I will assuming sum(Sales) could be 0 in some scenarios, so to determine null, you can use the date or count record of Sales/count record of Sales that are > 0.

1) If( num([SALES_DATE.autoCalendar.Month]) <= num(month(today())), Sum({<[SALES_DATE.autoCalendar.Year] = {'2021'}>}SALES))

2) If(Count({<[SALES_DATE.autoCalendar.Year] = {'2021'}, SALES ={">0"} >}SALES) > 0,  Sum({<[SALES_DATE.autoCalendar.Year] = {'2021'}>}SALES))

[alanwong1178]

1) For first expression , it works well if no value is selected in filter : [SALES_DATE.autoCalendar.YearMonth]

But my wish is to ignore this filter so that if any value selected in this filter will not affect the result in the table. This means no matter user select anything in [SALES_DATE.autoCalendar.YearMonth], it shows data from year start to current month.

I tried to modify the expression but it does not work as i wish

If( num([SALES_DATE.autoCalendar.Month]) <= num(month(today())), Sum({<[SALES_DATE.autoCalendar.Year] = {'2021'},[SALES_DATE.autoCalendar.YearMonth],[SALES_DATE.autoCalendar.Month]>}SALES))

2) For second expression , this does not work because consider that there is 0 value for Feb21 in my case, so this will be null . I would like to be if it is between year start to current month , it shows sales and 0 if null. If it is greater than current month , then it shows null()

[stevejoyce]

Ignore the same fields in your if-condition.  Without it, months not in your selection will all return null and result will display null.

If( num({<[SALES_DATE.autoCalendar.YearMonth],[SALES_DATE.autoCalendar.Month]>} [SALES_DATE.autoCalendar.Month]) <= num(month(today())), Sum({<[SALES_DATE.autoCalendar.Year] = {'2021'},[SALES_DATE.autoCalendar.YearMonth],[SALES_DATE.autoCalendar.Month]>}SALES))