Show [Unit Cost] through time

DAX DAX Calculations
1

Hi all,

I’m trying to find a way to visualize item [Unit Cost] trough time. In order to do so effectively I would need to be able to show a value for every single day since the item first had a [Unit Cost] Value <> 0,00 before that time it should return a BLANK.
After that first time - on all dates the [Unit Cost] didn’t change I want to show the last known [Unit Cost] Value <>0. So if a change resulted in a Value =0 then I want to show the last known [Unit Cost] Value <>0 before that.

I’ve worked out what the previous date was where the [Unit Cost] <>0. But so far that didn’t get me anywhere… Any help will be much appriciated!
Attached the PBX-file Track Unit Cost trough time.pbix (159.5 KB)

Below an image of what I hope to achieve in Power BI

Unit%20Cost
Unit Cost.PNG747×581 28.2 KB

2

Try this.

Change the previous date formula to the below

Previous date UC<>0 = 
CALCULATE( MAX( tDates[Date] ),
    FILTER(
        FILTER( ALL( tDates ), tDates[Date] <= MAX( tDates[Date] ) ),
            NOT( ISBLANK( [Total Costs] ) )
            ))

Then create this formula

Total Unit Cost = 
VAR PreviousSales = [Previous date UC<>0]

RETURN
CALCULATE( [Unit Cost],
    FILTER( ALL( tDates ), tDates[Date] =  PreviousSales ))

See below for results

image
image.png642×698 25.1 KB

Think this should do it.

3

Perfect!
Just added a condition to deal with a Value=0

Previous date UC<>0 =
CALCULATE( MAX( tDates[Date] ),
FILTER(
FILTER( ALL( tDates ), tDates[Date] <= MAX( tDates[Date] ) ),
NOT( ISBLANK( [Total Costs] ) ) && [Total Costs] <> 0
))

Thank you Sam

4

Nice thanks

5

I found this useful to solve the issue I was facing. I have a set of projects whose status are updated in a log table. I want to show in a matrix for each project the status related to the last update.
Using your suggestions I made it work, but since I have to use a scalar (the last updated status) I used the LASTNOTBLANK. I’m sure there is a smarter way to handle formulas like this.
Thanks!
ProjectLogGantt.pbix (36.6 KB)