Skip to main content
cancel
Showing results for 
Search instead 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

Reply
vasilsabev
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: 

1.JPG

 

I need to display this:

 

 2.JPG

 

 

 

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
v-cherch-msft
Employee
Employee

Hi @vasilsabev

 

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] ) ) )
)

1.png

 

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.

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

 

PREVIOUSMONTH(dates) ? 

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.

Helpful resources

Announcements
Europe Fabric Conference

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 Carousel June 2024

Power BI Monthly Update - June 2024

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

RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

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