cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

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

#### Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

#### Power BI Monthly Update - June 2024

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

#### New forum boards available in Real-Time Intelligence.

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

Top Solution Authors
Top Kudoed Authors