Slicer to show data only if I have there data for last 13 months

[AndrejZitnay]

Hello all,

I have my data set where I am showing distance travel per each vehicle for last 13 months.

I am using Calendar table where I have slicer for last 13 mohths.

I don't have problem with .

My data Calendar - I have slicer to filter last 13 moths.

Now I need to show data only if there are data for last 13 months.

Vehicle A might have  resutls only for latest 6 months

Vehicle B might have results for full last 13 monhts

I need to avoid vehicles/resutls if there aren't data for last 13 months. 

Is there way to do that?

I have distances in my DistanceTable and I link that to MasterCalendar via Date.

Each row is daily entry for one vehicle for one day.

I need to find way to find slicer / fitler to show only vehicles if there is some distance entry in each month of last 13 months.

Or another way around. I need to exclude vehicles if there aren't data in each month for last 13 moths.

Would you be so kind and help me out?

Thanks.

Andrej

[v-yiruan-msft · Accepted solution]

Hi @AndrejZitnay ,

I created a sample pbix file(see attachment), please check whether that is what you want.

1. Create a calculated column in the fact table to get the yearmonth

YearMonth = VALUE(CONCATENATE(YEAR('Table'[Month]),FORMAT('Table'[Month],"MM")))

2. Create a measure as below to judge whether the vehiche include the data from last 13 months: if yes, return 1. otherwise,  return 0.

Note: I just go back 13 months based on the current date...

Has the data for last 13 months = 
VAR _countofmonths =
    CALCULATE (
        DISTINCTCOUNT ( 'Table'[YearMonth] ),
        ALLEXCEPT ( 'Table', 'Table'[Vehicle] ),
        DATESBETWEEN (
            'Table'[Month],
            EOMONTH ( TODAY (), -14 ) + 1,
            EOMONTH ( TODAY (), -1 )
        )
    )
RETURN
    IF ( _countofmonths < 13, 0, 1 )

3. Create visual level filter with condition (Has the data for last 13 months is 1)

Best Regards

[amitchandak]

@AndrejZitnay , Create a measure like this and try

sumx(filter(summarize(Table, Table[Vehicle], "_1", DistinctCount(Table[Month year]), "_2", sum(Table[Distance Miles])),[_1]=13),[_2])