Count based on a specific interval is giving other dates

[AmiraBedh]

I am calculating the count of tickets based on the following condition : 

The partitions date is a column giving the dates of each data load. The max partition is 08/05/2022

I want to calculate the count of tickets with the status open and fk_ticket_creation_date between the date of the most recent partition which is 08/05/2022

and the first date of the year - 1 of the partition date : 01/05/2021

OpenCount =

var _max = max(dim_snapshot[Partitions])

var _min = eomonth(max(dim_snapshot[Partitions]),-12)+1

return

CALCULATE (

DISTINCTCOUNT(fct_ticket[fk_ticket]),

dim_status[status] in {"OPEN"},

USERELATIONSHIP(dim_calendrier[id],fct_ticket[fk_ticket_creation_date]),

FILTER (dim_calendrier, dim_calendrier[id] <= _max && dim_calendrier[id] >= _min) )

Is the condition included in the measure correct ?

Because when I put my measure in a visual it is showing other months without values and they don't belong to the interval I mentioned in the dax formula :

What should I do to show in the visual only this interval : months starting from May 2021 until May 2022 like specified in the measure.

[v-yanjiang-msft]

Hi @AmiraBedh ,

Has your problem been solved? If you need further help, please feel free to let me know.

Best Regards,
Community Support Team _ kalyj

[AmiraBedh]

It is always the same issue. showing data for all months and not only the interval.

[v-yanjiang-msft]

Hi @AmiraBedh ,

Thanks for your reply.

Really weird. Have you checked the "Show items with no data"?

If this is not the case, I guess it's related to the relationship between tables. Maybe need a sample file without sensitive data to dig.

Best Regards,
Community Support Team _ kalyj

[v-yanjiang-msft]

Hi @AmiraBedh ,

Is your problem solved?? If so, Would you mind accept the helpful replies as solutions? Then we are able to close the thread. More people who have the same requirement will find the solution quickly and benefit here. Thank you.

Best Regards,
Community Support Team _ kalyj

[v-yanjiang-msft]

Hi @AmiraBedh ,

In your formula, actually all the other dates out of the specific interval return a value of 0. If you don't want to display them in the visual, you should modify the formula like this:

OpenCount =
VAR _max =
    MAX ( dim_snapshot[Partitions] )
VAR _min =
    EOMONTH ( MAX ( dim_snapshot[Partitions] ), -12 ) + 1
RETURN
    IF (
        dim_calendrier[id] <= _max
            && dim_calendrier[id] >= _min,
        CALCULATE (
            DISTINCTCOUNT ( fct_ticket[fk_ticket] ),
            dim_status[status] IN { "OPEN" },
            USERELATIONSHIP ( dim_calendrier[id], fct_ticket[fk_ticket_creation_date] ),
            FILTER (
                dim_calendrier,
                dim_calendrier[id] <= _max
                    && dim_calendrier[id] >= _min
            ),
            BLANK ()
        )
    )

This will only return the below part from your visual. This visual actually count the whole fk_ticket number within the interval, it's OK if it's just your expected result.

But if you want to get the count of each month with the interval seperately, you should modify the formula like this:

OpenCount =
VAR _max =
    MAX ( dim_snapshot[Partitions] )
VAR _min =
    EOMONTH ( MAX ( dim_snapshot[Partitions] ), -12 ) + 1
RETURN
    IF (
        dim_calendrier[id] <= _max
            && dim_calendrier[id] >= _min,
        CALCULATE (
            DISTINCTCOUNT ( fct_ticket[fk_ticket] ),
            dim_status[status] IN { "OPEN" },
            USERELATIONSHIP ( dim_calendrier[id], fct_ticket[fk_ticket_creation_date] ),
            BLANK ()
        )
    )

Best Regards,
Community Support Team _ kalyj

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[AmiraBedh]

I tried to follow you code but I am receiving this error :  Cannot find name [id]

[v-yanjiang-msft]

Hi @AmiraBedh ，

Please try to add MAX before the id column.

MAX(dim_calendrier[id]) <= _max
            && MAX(dim_calendrier[id]) >= _min

Best Regards,
Community Support Team _ kalyj

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[tamerj1]

Hi @AmiraBedh 

please try

OpenCount =
VAR _max =
    MAX ( dim_snapshot[Partitions] )
VAR _min =
    EOMONTH ( _max, -12 ) + 1
RETURN
    CALCULATE (
        DISTINCTCOUNT ( fct_ticket[fk_ticket] ),
        dim_status[status] IN { "OPEN" },
        dim_calendrier[id] <= _max,
        dim_calendrier[id] >= _min
    )

[AmiraBedh]

The measure is working but when I put it in a visual I get like this :

I am using two columns from dim_calendar :

• calendar_year_month : having the values in the following format : Jan-2021, Feb-2021...
• year_month : having the values in the following format 202101, 202102 to sort the visual

The issue when I add the year_month in the tooltip to sort the visual it shows me the extra months instead of the interval I want : for example May 2021 - May 2022 

[tamerj1]

Hi @AmiraBedh 
Please try

OpenCount =
VAR _max =
    MAX ( dim_snapshot[Partitions] )
VAR _min =
    EOMONTH ( _max, -12 ) + 1
RETURN
    CALCULATE (
        DISTINCTCOUNT ( fct_ticket[fk_ticket] ),
        REMOVEFILTERS ( dim_calendrier ),
        dim_status[status] IN { "OPEN" },
        dim_calendrier[id] <= _max,
        dim_calendrier[id] >= _min
    )

[tamerj1]

@AmiraBedh 
Have you tried above solution?