How to prevent displaying future months in a calculation with blank = 0

[JCUP]

Hello,

I'm using the following function in order to make the charts display the periods that don't have a value. But the problem now is that it is displaying the months in the future.

x Count Tickets = 
                IF
                    (
                        ISBLANK
                            (
                                DISTINCTCOUNT('Tickets DB'[Número de Ticket])
                            )
                        ,0,
                        DISTINCTCOUNT('Tickets DB'[Número de Ticket])
                    )

Months in the Future display

How can I avoid the months in the future from displaying?

Thanks in advance.

[v-juanli-msft · Accepted solution]

Hi @JCUP 

You could create a measure

Measure = IF(DATEDIFF(MAX('Table'[date]),TODAY(),MONTH)>0,IF(ISBLANK(SUM('Table'[value])),0,SUM('Table'[value])))

replace "sum" with "DISTINCTCOUNT" for your scenario

Best Regards
Maggie

Community Support Team _ Maggie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[JCUP]

@v-juanli-msft 

I was exploring and is not working.

When I have selections without data in a month (is blank), it is not calculating. Check the next image:

[JCUP]

Thank you very much! It works fine.

If I understand it right, what we did is "IF" the date difference in months is greater than zero, then it calculates the formula "isblank...", else we do nothing.

For the previous months it will get a date difference in months greater than 0, for current month, and those in the future, it will display 0 (and won't calculate, therefore won't return a 0 or anything).

At first, I thought it would display negative values for the months in the future but it displays only zeros.

The inconvenient here is that it will not include current month and i already have data for it. I mean, it won't display September (we are at 2019-09-04)

What I did is to change the datediff period for days and now I can see september 😄

Thank you very much @v-juanli-msft .

I am adding all this explanation and the final measure, for anyone that might find it useful and to make sure I understood properly.

x Count Tickets = 
IF
    (
        DATEDIFF(MAX('Tickets DB'[Fecha]),TODAY(),DAY)>0,
                        IF
                            (
                            ISBLANK
                                (
                                    DISTINCTCOUNT('Tickets DB'[Número de Ticket])
                                )
                            ,0,
                            DISTINCTCOUNT('Tickets DB'[Número de Ticket])
                            )
    )