Need Help in DAX

[sumon_51]

i have fact table where have data for various date, i have made a calendar table using calendar() and max and min,and link this calendar table to fact table,  from this calendar table i have made 2 slicer month and date
my requirement:
1. if i select a date from my 2 slicer ie. from month i select sept and from date slicer i select 23, i want a dax that will show previous available date in card, that is 18 sept, as there is no data for 19-22 sept, is this possible 

[FBergamaschi · Accepted solution]

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

Imponibile Prev Day =

VAR SelectedDate = SELECTEDVALUE( 'Calendar'[Date] )

VAR PrevDatewithSales =

CALCULATE(

    MAX ( Sales[OrderDate] ),

    Sales[OrderDate] < SelectedDate,

    REMOVEFILTERS( 'Calendar' )

)

RETURN

CALCULATE(

    [YourMeasure],

    'Calendar'[Date] = PrevDatewithSales

)

If this helped, please consider giving kudos and mark as a solution

@me in replies or I'll lose your thread

Want to check your DAX skills? Answer my biweekly DAX challenges on the kubisco Linkedin page

Consider voting this Power BI idea

Francesco Bergamaschi

MBA, M.Eng, M.Econ, Professor of BI

[danextian · Accepted solution]

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

[raja1992 · Accepted solution]

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.

[danextian · Accepted solution]

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

[sumon_51]

Thank for you solution
i need an extra solution 

1. if select jan-4 my card will show total sales for jan 4, if jan 4 has no data then it will show blank. if blank or not blank, need % changes compare with previous available date, for example i select jan 5 value is 1119 as jan 4 has no value so it will consider jan 3 value is 393 to compare and result should be 184.73 %

[FBergamaschi]

Is not this the diference in pct between

[YourMeasure] and 

[YourMeasurePrev Day] =

VAR SelectedDate = SELECTEDVALUE( 'Calendar'[Date] )

VAR PrevDatewithSales =

CALCULATE(

    MAX ( Sales[OrderDate] ),

    Sales[OrderDate] < SelectedDate,

    REMOVEFILTERS( 'Calendar' )

)

RETURN

CALCULATE(

    [YourMeasure],

    'Calendar'[Date] = PrevDatewithSales

)

?

If yes,

DIVIDE (

          [YourMeanure] - [YourMeasurePrev Day],
          [YourMeasurePrev Day]
)

If this helped, please consider giving kudos and mark as a solution

@me in replies or I'll lose your thread

Want to check your DAX skills? Answer my biweekly DAX challenges on the kubisco Linkedin page

Consider voting this Power BI idea

Francesco Bergamaschi

MBA, M.Eng, M.Econ, Professor of BI

[FBergamaschi · Accepted solution]

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

Imponibile Prev Day =

VAR SelectedDate = SELECTEDVALUE( 'Calendar'[Date] )

VAR PrevDatewithSales =

CALCULATE(

    MAX ( Sales[OrderDate] ),

    Sales[OrderDate] < SelectedDate,

    REMOVEFILTERS( 'Calendar' )

)

RETURN

CALCULATE(

    [YourMeasure],

    'Calendar'[Date] = PrevDatewithSales

)

If this helped, please consider giving kudos and mark as a solution

@me in replies or I'll lose your thread

Want to check your DAX skills? Answer my biweekly DAX challenges on the kubisco Linkedin page

Consider voting this Power BI idea

Francesco Bergamaschi

MBA, M.Eng, M.Econ, Professor of BI

[sumon_51]

@Shahid12523