How to display the latest value in the matrix column totals ?

[Anonymous]

Hello everybody, 

I am having the requirement to dispaly the latest value in the row totals. The data is a non-additive measure. It is customer snapshots, so the taks is to display the count of active customers per month, but if the whole year is filtered - meaning from Jan to Dec and we have data only up until July, the total should show the July's data

Filter min date - 1st of Jan 

Filter max date - 31st of Dec

I have this data: 

I need to display this:

 

The measure I am using is the follwing:

Customers :=
CALCULATE (
    SUM ( fact_table[col] ),
    FILTER ( dim_table, dim_table[col] = "Yes" ),
    FILTER (
        ALL ( fact_table[date] ),
        CONTAINS ( VALUES ( dim_time[date] ), dim_time[date], fact_table[date] )
    )
)

I would be grateful of anyone can help.

Best regards, 

Vasil

[v-cherch-msft]

Hi @Anonymous

You may try to use MAX Function to get the latest date. Here is the reference for you.

https://blogs.msdn.microsoft.com/lukaszp/2015/08/08/finding-the-latest-date-in-power-bi-desktop/

Latest =
CALCULATE (
    SUM ( Table1[values] ),
    FILTER ( Table1, MONTH ( Table1[date] ) = MONTH ( MAX ( Table1[date] ) ) )
)

Regards,

Cherie

[Anonymous]

Even if I enable the time relation, any idea how should this be done with 

PREVIOUSMONTH(dates) ? 

[Anonymous]

Hi, 

Thanks for the answer. Unfortunatelly this measure works only when there is a physical relationship with the time dimension. 

In this model I am using a virtual one (https://www.sqlbi.com/articles/physical-and-virtual-relationships-in-dax/)

Any idea how would it work with virtual relation to the time ? 

If I do it like you said I get only the last month in the table.