Forum Discussion

sumon_51's avatar
sumon_51
Regular Visitor
10 months ago
Solved

Need Help in DAX

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 

Need Help
  • FBergamaschi's avatar
    FBergamaschi
    10 months ago

    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's avatar
    danextian
    10 months ago

    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's avatar
    raja1992
    10 months ago

    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.

6 Replies

  • danextian's avatar
    danextian
    Super User
    10 months ago

    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's avatar
      sumon_51
      Regular Visitor
      10 months ago

      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's avatar
        FBergamaschi
        Super User
        10 months ago

        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's avatar
    FBergamaschi
    Super User
    10 months ago

    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

  • raja1992's avatar
    raja1992
    Resolver I
    10 months ago

    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.