cancel
Showing results for 
Search instead for 
Did you mean: 

Fabric is Generally Available. Browse Fabric Presentations. Work towards your Fabric certification with the Cloud Skills Challenge.

Reply
czaldumbide
Helper I
Helper I

calculating sum for max date

I created a matrix with the product name as rows, and the sum of amount for the latest purchase date as the value. 

 

I am using the following measure for my value: 

CALCULATE(SUM('Sales'[Amount]), FILTER ('Sales', 'Sales'[Date] = MAX('Sales'[Date] )))

 

Even though the matrix displays the correct grand total in the table, I still get product names that do not have a record for the latest date of my table, and instead of showing a BLANK value they display the SUM('Sales'[Amount]) that belongs to the latest date that product appeared in the table.

 

Lets say my latest date of the table is 11/05/2020. Let me modify my measure to be the following:

CALCULATE(SUM('Transactions'[Amount]), FILTER ('Transactions', 'Transactions'[Date] = DATE(2020,05,11))

 

This measure does give me the right matrix, but I want my  latest date to be automated. Can anyone help me understand why is this happening and what I can do to fix it. 

 

Thank you!

 

2 ACCEPTED SOLUTIONS
AlB
Super User
Super User

Hi @czaldumbide 

The MAX() in your measure will give you the latest date for the product in that row, since products is part of the filter context. Try this:

Measure =
VAR latestdate_ =
    MAX ( ALL ( 'Sales'[Date] ) )
RETURN
    CALCULATE (
        SUM ( 'Sales'[Amount] ),
        FILTER ( 'Sales', 'Sales'[Date] = latestdate_ )
    )

 Please mark the question solved when done and consider giving kudos if posts are helpful.

Contact me privately for support with any larger-scale BI needs

Cheers 

SU18_powerbi_badge

View solution in original post

Anonymous
Not applicable

There are several things wrong with your code.

 

First, you are creating a filter out of the whole expanded fact table. THIS IS VERY, VERY BAD and slows down calculations tremendously. If you start doing such calculations on big fact tables, you'll feel the heat instantly. One of the golden rules of DAX programming says: Never filter a table if you can filter a column.

 

Secondly, you should have a Calendar dimension in your model.

 

If you do have a proper Calendar, then you can write:

[Latest Sales Amount] =
var __latestSalesDate =
	CALCULATE(
		// This is the very latest
		// day with any sales in it
		// with no regard to any
		// selections in any slicers.
		MAX( Sales[Date] ),
		ALL( Sales )
	)
var __result = 
	CALCULATE(
		SUM( Sales[Amount] ),
		// This only works correctly
		// if Calendar is THE date table
		// in the model.
		Calendar[Date] = __latestSalesDate
	)
return
	__result


Best
D

View solution in original post

6 REPLIES 6
Anonymous
Not applicable

@amitchandak @AlB 

 

I have a similar situation for aggregated column. How to create it? Please help

 

Measure =
VAR latestdate_ =
MAX ( ALL ( 'Sales'[Date] ) )
RETURN
CALCULATE (
SUM ( 'Sales'[Amount] ),
FILTER ( 'Sales', 'Sales'[Date] = latestdate_ )

 

Anonymous
Not applicable

There are several things wrong with your code.

 

First, you are creating a filter out of the whole expanded fact table. THIS IS VERY, VERY BAD and slows down calculations tremendously. If you start doing such calculations on big fact tables, you'll feel the heat instantly. One of the golden rules of DAX programming says: Never filter a table if you can filter a column.

 

Secondly, you should have a Calendar dimension in your model.

 

If you do have a proper Calendar, then you can write:

[Latest Sales Amount] =
var __latestSalesDate =
	CALCULATE(
		// This is the very latest
		// day with any sales in it
		// with no regard to any
		// selections in any slicers.
		MAX( Sales[Date] ),
		ALL( Sales )
	)
var __result = 
	CALCULATE(
		SUM( Sales[Amount] ),
		// This only works correctly
		// if Calendar is THE date table
		// in the model.
		Calendar[Date] = __latestSalesDate
	)
return
	__result


Best
D

hi,  CALCULATE(SUM('Sales'[Amount]), FILTER ('Sales', 'Sales'[Date] = MAX('Sales'[Date] ))) 

the same metric, wen i pulled in , it is blank and it needs always date column to b pulled in along wit it , to get the data. Is there a way to populate the amount for max date without pulling the datekey in the report rows?

Frank_Fei
Resolver I
Resolver I

Hi @czaldumbide ,

 

Measure =
VAR latestdate_ =
    CALCULATE (
        MAX ( 'Sales'[Date] ),
        ALLSELECTED ( 'Sales' ),
        VALUES ( 'Sales'[product] )
    )
RETURN
    CALCULATE (
        SUM ( 'Sales'[Amount] ),
        FILTER ( 'Sales', 'Sales'[Date] = latestdate_ )
    )

 

amitchandak
Super User
Super User

@czaldumbide , try like

measure =
var _max = MAXX('Sales','Sales'[Date] )
return
CALCULATE(SUM('Sales'[Amount]), FILTER ('Sales', 'Sales'[Date] =_max ))

AlB
Super User
Super User

Hi @czaldumbide 

The MAX() in your measure will give you the latest date for the product in that row, since products is part of the filter context. Try this:

Measure =
VAR latestdate_ =
    MAX ( ALL ( 'Sales'[Date] ) )
RETURN
    CALCULATE (
        SUM ( 'Sales'[Amount] ),
        FILTER ( 'Sales', 'Sales'[Date] = latestdate_ )
    )

 Please mark the question solved when done and consider giving kudos if posts are helpful.

Contact me privately for support with any larger-scale BI needs

Cheers 

SU18_powerbi_badge

Helpful resources

Announcements
PBI November 2023 Update Carousel

Power BI Monthly Update - November 2023

Check out the November 2023 Power BI update to learn about new features.

Community News

Fabric Community News unified experience

Read the latest Fabric Community announcements, including updates on Power BI, Synapse, Data Factory and Data Activator.

Power BI Fabric Summit Carousel

The largest Power BI and Fabric virtual conference

130+ sessions, 130+ speakers, Product managers, MVPs, and experts. All about Power BI and Fabric. Attend online or watch the recordings.

Top Solution Authors
Top Kudoed Authors