CODEWITHSUNDEEP
powerbi-desktop
RaRdEvA
RaRdEvA

Reputation: 766

Power bi How can i display items without data with a zero AND hide the months in the future

I'm using the following measure 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])
                )

enter image description here

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

I also tried the following measure:

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])
                            )
    )

But it is not displaying months without data

enter image description here

Thanks in advance.

Upvotes: 3

Views: 4855

Answers (4)

StelioK
StelioK

Reputation: 1781

Create a calendar table using something like the following: 

Calendar = 
VAR MinDate = MIN('Tickets DB'[Fecha])
VAR MaxDate = MAX('Tickets DB'[Fecha])
VAR BaseCalendar =
    CALENDAR ( MinDate, MaxDate )
RETURN
    GENERATE (
        BaseCalendar,
        VAR Basedate = [Date]
        VAR YearDate =
            YEAR ( Basedate )
        VAR MonthNumber =
            MONTH ( Basedate )
        VAR YrMonth = 100 * YearDate + MonthNumber
        VAR Qtr =
            CONCATENATE ( "Q", CEILING ( MONTH ( Basedate ) / 3, 1 ) )
        VAR QtrYr = YearDate & " " & Qtr
        RETURN
            ROW (
                "Day", Basedate,
                "Year", YearDate,
                "Month Number", MonthNumber,
                "Month", FORMAT ( Basedate, "mmmm" ),
                "Year Month", FORMAT ( Basedate, "mmm yy" ),
                "YrMonth", YrMonth,
                "Qtr", Qtr,
                "QtrYr", QtrYr
            )
    )

If you have dates in the future in your table, replace the MAX('Tickets DB'[Fecha]) part to NOW() then just create a relationship from your 'Tickets DB' table to this calendar table on both date fields and that should correct the issue (and allow you to use time intelligence functions). Once this is set up correctly your measure should work just fine.

Hope it helps.

Upvotes: 1

greggyb
greggyb

Reputation: 3798

IF (
    MAX ( 'DimDate'[Date] ) > TODAY (),
    BLANK (),
    DISTINCTCOUNT ( 'Tickets DB'[Número de Ticket] ) + 0
)

Starting from your original measure, we check whether the date in context is after today's date. In that case, we blank the measure. Otherwise we do your DISTINCTCOUNT, which may possibly return a blank value. Note that DAX blanks are not equivalent to SQL NULLs - specifically addition with a blank returns a number, unless both addends are blank; so BLANK () + 0 = 0. This neatly solves your display for months with no data. But we only execute this logic for past months.

Note, I've written this assuming that you have a date table. If you don't, you should get one. I like mine, but there are plenty of good ones out there.

Upvotes: 2

Jelle Hoekstra
Jelle Hoekstra

Reputation: 672

If it is always the dates in the future you want to hide, you could opt for relative date filtering on your visual. More info here

enter image description here

Upvotes: 1

Marcelo Martins
Marcelo Martins

Reputation: 21

1. First create a calculated column(calendar dimension table), to return if there is a record on all dates of this dimension

example of calculated column

2. Create the measurement below ( Where you will change the context of the measurement to bring only the true fields from the column you created earlier)

   Medida Stack = 
    CALCULATE(
        DISTINCTCOUNT(
            fVendas[CLIENTE/LOJA]);
            CALCULATETABLE(
                dCalendario;
                dCalendario[Has Sales?]=TRUE()
            )
    )

fvendas[Cliente/loja] = 'Tickets DB'[Número de Ticket] dCalendario = calendar dimension table

Upvotes: 1

Related Questions