Need Help in DAX

Hi sumon_51 ,

first of all you need to select also a year (unless you loaded only facts from a single year but in any case it is better to fix a year or in furture refreshes if you include multiple years fact you get an issue)

That said, here is the code

Hi sumon_51 

This measure will return the latest date with data on or before the currently selected date

Latest Date With Value = 
CALCULATE (
        // Find the latest date (up to the currently selected date)
        // where [Sum of Sales] is not blank
        LASTNONBLANK ( Dates[Date], [Sum of Sales] ),
        FILTER (
            ALL ( Dates ),                  // remove filters to scan the full date range
            Dates[Date] <= MAX ( Dates[Date] ) // only keep dates up to the current context date
        )
    )

Below will return a value based on the date above

Sales - Latest Available Date = 
VAR LastNonBlankDate =
    CALCULATE (
        // Find the latest date (up to the currently selected date)
        // where [Sum of Sales] is not blank
        LASTNONBLANK ( Dates[Date], [Sum of Sales] ),
        FILTER (
            ALL ( Dates ),                  // remove filters to scan the full date range
            Dates[Date] <= MAX ( Dates[Date] ) // only keep dates up to the current context date
        )
    )
RETURN
    CALCULATE (
        // Return the sales value corresponding to that last nonblank date
        [Sum of Sales],
        FILTER (
            ALL ( Dates ),                  // remove filters to allow exact match
            Dates[Date] = LastNonBlankDate  // isolate only the last valid date found above
        )
    )

 In the image below, I selected Jan 4 which doesn't have a value so it shows the value for Jan 3 instead

I had a similar case in my sales fact table where data wasn’t available for all calendar dates. For example, my calendar showed 19th–22nd Sept, but sales were only on 18th and 23rd.

What I did was create a measure to pick the last available date before the selected date. Here’s a DAX pattern that worked for me:

PrevAvailableDate =
CALCULATE (
    MAX ( FactTable[Date] ),
    FILTER (
        ALL ( FactTable ),
        FactTable[Date] < MAX ( 'Calendar'[Date] )
    )
)

• MAX('Calendar'[Date]) → picks the selected date from the slicer (like 23-Sep).

• The FILTER goes back into the fact table and finds the largest date that is less than the selected date (in your case, 18-Sep).

Example from my model:

• Calendar selected = 23-Sep

• Fact table dates = 15, 18, 23 Sept

• Measure result = 18-Sep ✅

Just drop this measure in a card visual and it will always show the latest available date before the slicer date.

Small tip: If you also want to display the actual value from that date (like Sales), just replace MAX(FactTable[Date]) with your measure inside the same pattern.