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

Enhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.

Reply
jpmakako
Frequent Visitor

Trouble with Calculated Column with Dates

Hi, I have two tables. One with Inventory of "Units" (and their respective "Projects"), "Price Unit" and "When Date" the "Units" were sold, the other table "DatePrices" shows the dates with different prices for different dates to each of this unit. Im trying to bring the Value each "Unit" should have been priced by looking at the oldest list price {less or equal to} "When Date" it was sold. I have the following DAX in the inventory table trying to bring the Date of the list the column is bringing the value from:

 

Latest Date = VAR Proy = [Projects] VAR Unit = [Units] VAR DateS = [When Date]
VAR MaxDate =
    CALCULATE(
        MAX(DatePrices[Date]),
        DatePrices[Unit] = Unit,
        DatePrices[Project] = Proy,
        DatePrices[Date] <= DateS      
    )
RETURN
    MaxDate
 
and 
 
Should Price = VAR Proy = [Projects] VAR Unit = [Units] VAR DateS = [When Date]
VAR MaxDate =
    CALCULATE(
        MAX(DatePrices[Date]),
        DatePrices[Unit] = Unit,
        DatePrices[Project] = Proy,
        DatePrices[Date] <= DateS      
    )
RETURN
CALCULATE(
       
CALCULATE(
        SUM(DatePrices[Price]),
        DatePrices[Unit] = Unit,
        DatePrices[Project] = Proy,
        DatePrices[Date] = Max Date       
    )
The problem is the value in the columns only works when the value of "When Date" is exactly the same as the DatePrices[Date]. As if the "<=" was "=". I thought it was comparing the date as text when going through the Var DateS and tried DatePrices[Date] <= Date(Year(DateS),Month(DateS),Day(DatesS) but the results are the same. Any ideas what Im missing?
 
[I tried  EVALUATE
//{CALCULATE( MAX(DatePrices[Date]),
FILTER(DatePrices,
        DatePrices[Unit] = "1001-2" && 
        DatePrices[Project] = "Praia"  
        && DatePrices[Date] <= DATE(2023,3,22)      
    )//)} in Dax Studio and works as it should]     
1 ACCEPTED SOLUTION
jpmakako
Frequent Visitor

I used this instead and worked, no idea why.

 

Should Price = VAR Proy = [Projects] VAR Unit = [Units] VAR DateS = [When Date]
VAR MaxDate =
    MAXX(FILTER( DatePrices,
        DatePrices[Unit] = Unit &&
        DatePrices[Project] = Proy &&
        DatePrices[Date] <= DateS)
    , DatePrices[Date]
    )
RETURN
    SUMX(FILTER( DatePrices,
        DatePrices[Unit] = Unit &&
        DatePrices[Project] = Proy &&
        DatePrices[Date] = MaxDate)
    , DatePrices[Price]
    )

View solution in original post

2 REPLIES 2
jpmakako
Frequent Visitor

I used this instead and worked, no idea why.

 

Should Price = VAR Proy = [Projects] VAR Unit = [Units] VAR DateS = [When Date]
VAR MaxDate =
    MAXX(FILTER( DatePrices,
        DatePrices[Unit] = Unit &&
        DatePrices[Project] = Proy &&
        DatePrices[Date] <= DateS)
    , DatePrices[Date]
    )
RETURN
    SUMX(FILTER( DatePrices,
        DatePrices[Unit] = Unit &&
        DatePrices[Project] = Proy &&
        DatePrices[Date] = MaxDate)
    , DatePrices[Price]
    )
Wilson_
Super User
Super User

Hello jpmakako,

 

I would be happy to try to help. Can you share a sample pbix file?




Did I answer your question? Mark my post as a solution!

Proud to be a Super User!





Helpful resources

Announcements
July 2025 community update carousel

Fabric Community Update - July 2025

Find out what's new and trending in the Fabric community.

July PBI25 Carousel

Power BI Monthly Update - July 2025

Check out the July 2025 Power BI update to learn about new features.

Join our Fabric User Panel

Join our Fabric User Panel

This is your chance to engage directly with the engineering team behind Fabric and Power BI. Share your experiences and shape the future.