Keep Filter on dax Measure

[edmar_502]

Hello Guys,

So I have a requirement, I get a table with a count attendance by the system, so there is a measure called the previous month calculated by count attendance, for example, if it's may-2020 the value will be by march-2020 it's dynamic, I don't need to select.

So when I put everything on the table the values don't show the correct value. Take a look at the image above:

when I select the last month 05 = May-2020 the correct value appears.

However, when I uncheck the value on the filter the values show not correct.

Is there a way to show the correct value keep the filter previous month without the need to select the month in the filter?

Dax Measure: 

CALCULATE(

[Quantidade de Atendimentos],

PREVIOUSMONTH('Calendário_Ocorrência'[Data])

)

I really appreciate any suggestions and help.

Thanks.

[Anonymous]

@edmar_502: Add a column for your date in the table and it should illuminate the issue. It is hard to know why the issue is occurring without seeing the relationships.

[edmar_502]

Hello, Thanks for your fast reply:

Relationship:

Diagram

Expected Result:

When I checked the month equal 05 May i see the value by previous Month April, but when I unchecked the month the value showed the total by year. 

I would like to see the value of 6548.

Quantidade de Atendimentos = SUM('Consolidado Atendimento'[QTD_ATENDIMENTOS])

Atendimento Mês Anterior =

CALCULATE(

[Quantidade de Atendimentos],

PREVIOUSMONTH('Calendário_Ocorrência'[Data])

)

let me know if you need more details.

best regards,

Edmar

[Anonymous]

Are you referencing your date value in your date table in the result table? Make sure to reference that, so that the scope of the DAX measure is in reference to the calendar date, not the transaction table date. The core problem is that the DAX measure isn't scoped properly.

[edmar_502]

I am referencing the date field of the date table the value is repeated for the dates because I only want the total value of the Month. The point is that without the date I add the values of that selected month and in the other column I show the value of the previous month, however, I would like to get away to have the value of the previous month without having to select the month in the filter, that has the previous dynamic month, always taking into account the current base month.
See in the example I just want to show the values for the last month 05.
Is there any way to get this dynamically in the Dax metric? Would you have any ideas that help me?

[v-gizhi-msft · Accepted solution]

Hi,

Please try this measure without any related calendar table:

Previous Month = 
IF (
    MAX ( 'Table'[Month] ) = 1,
    CALCULATE (
        SUM ( 'Table'[Attendance] ),
        FILTER (
            ALLSELECTED ( 'Table' ),
            'Table'[Month] = 12
                && YEAR ( 'Table'[Date] )
                    = YEAR ( MAX ( 'Table'[Date] ) ) - 1
        )
    ),
    CALCULATE (
        SUM ( 'Table'[Attendance] ),
        FILTER ( ALLSELECTED ( 'Table' ), 'Table'[Month] = MAX ( 'Table'[Month] ) - 1 )
    )
)

The result shows:

See my attached pbix file.

Best Regards,

Giotto