Problem in Dax to create measure to find last cost by date

**Ramzan-ul-Mubarak** · 03-15-2022, 08:15 AM

I need help on Dax to create measure to find last value by date.
I have
1-a data table
2-a Unique names Table
and
3-the calendar table is in Data model.
Relationship is created in data model between
1-Date field in Data Table and Calendar
2-between Names field in Names table and Data Table.
I need to create a date-wise pivot table with quantity and cost fields in which i need the last cost value for null quantity / cost dates.
I have created one measure
=CALCULATE(Query1[Sum of Cost], LASTDATE('Calendar'[Date]))
but not working for blank quantity dates.
I have created another measure with dax
=IF ( HASONEVALUE ( Query2[Name] ), VAR vItem = VALUES (Query2[Name]) VAR vLastDate = MAX ( 'Calendar'[Date] ) RETURN CALCULATE( Max ( Query1[Cost] ), 'Calendar'[Date] <= vLastDate ))
but it is showing last maximum value instead of last latest value by date.

**XOR LX** · 03-15-2022, 08:54 AM

Hi,

PHP Code:


Last Cost Measure := 
VAR ThisDate = 
    MIN( 'Calendar'[Date] ) 
VAR LastDateWithValue = 
    CALCULATE( 
        MAX( 'Calendar'[Date] ), 
        FILTER( 
            ALLSELECTED( Query1 ), 
            Query1[Date] <= ThisDate 
                && NOT ( ISBLANK( Query1[Cost] ) ) 
        ) 
    ) 
RETURN 
    CALCULATE( MAX( Query1[Cost] ), 'Calendar'[Date] = LastDateWithValue )

Regards

**Ramzan-ul-Mubarak** · 03-16-2022, 01:29 AM

Dear XOR LX, thanks It is working. Wonder full.
Can you please help little more. I also need to find previous day cost. Means Last date minus 1. Please share the dax for that measure too.

**XOR LX** · 03-16-2022, 01:32 AM

If you can re-post your file with the expected results for this new requirement then I'll gladly help.

Regards

Problem in Dax to create measure to find last cost by date

LinkBack

Thread Tools

Rate This Thread

Display

Problem in Dax to create measure to find last cost by date

Re: Problem in Dax to create measure to find last cost by date

Re: Problem in Dax to create measure to find last cost by date

Re: Problem in Dax to create measure to find last cost by date

Thread Information

Users Browsing this Thread

Similar Threads

Trying to create measure to calculate for 'Sameperiodlastyear'

Problem with CONCATENATEX measure in pivot table

Pivot Table: Grand Total different measure from Column measure

[SOLVED] FIND PRICE COST from MAX DATE based on ITEM ID and SALE DATE

[SOLVED] Create a measure in Power Pivot to divide by value in subtotal row of the PP

find max $ of an item, same units of measure only

Please respond: Need to create additional measure in pivot table

Bookmarks

Bookmarks

Posting Permissions