Measure Moving Anual Total for frequency - Show just a period in a chart

[rfernandes]

Hello everyone,

I´m using this measure to calculate the Frequency in a Moving Anual Total period.

The database is from 2014 to 2019, i only what to show in the chart a period like 4/2017 to 4/2018.  When i plot it in a line char, the measure ignores  the visual level filter in the date in the chart, and show all years.

How can i filter a period? Do i have to change the measure?

Frequency MAT =
CALCULATE (
    CALCULATE (
        DIVIDE ( DISTINCTCOUNT ( b_dados[id_order] ); DISTINCTCOUNT ( b_dados[id_client] ) );
        DATESINPERIOD (
            b_dados[ano_mes_dia].[Date];
            LASTDATE ( b_dados[ano_mes_dia].[Date] );
            -1;
            YEAR
        )
    )
)

Thank you

[Anonymous]

Hi @rfernandes -

Do you have a table marked as a Date table? Then you should be able to filter as you want. 

The measure looks fine, except it appears that your dates refer to a fact table. 

Hope this helps,

Nathan

[rfernandes]

@Anonymous 

I m using the date from the fact table, is the only date in the model. I think when you mark the column as date, it creates automatically date table, but you cant see it. 

Other problem is how a date table looks like in a chart, is very different from the column date.

Date column from fact table and chart:

Date table and chart:

By the way, is there a way to show the line chart like the chart with date column?

Thank you

[Anonymous]

@rfernandes - It is a best practice to use dates in a separate date table. It looks like you already have DimCalendar? Otherwise, there are many examples online for creating a date table with DAX or M. You can define a column that combines the year and month and put that on the X axis in your line chart. Also, it appears the 2nd chart is drilled down to days.

[rfernandes]

I tried to filter with a calendar table and it doesn´t work.  If i filter, for example, the calendar[date] from 1/1/2017 to 31/12/2018, it stop using information from 2016 to calculate the MAT of 2017.

The second chart is with year and month from the calendar table, they are categorical. I have to create a new column in the calendar table with month and year, mark as date and use it in the X axis.