Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

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

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
RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

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

MayPowerBICarousel

Power BI Monthly Update - May 2024

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

LearnSurvey

Fabric certifications survey

Certification feedback opportunity for the community.