cancel
Showing results for
Did you mean:

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

Frequent Visitor

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

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

3 REPLIES 3
Employee

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

Community Support Team _ Cherie Chen
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Frequent Visitor

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

PREVIOUSMONTH(dates) ?

Frequent Visitor

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.

Announcements

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

Power BI Monthly Update - May 2024

Check out the May 2024 Power BI update to learn about new features.

Fabric certifications survey

Certification feedback opportunity for the community.

Top Solution Authors
Top Kudoed Authors