Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
2 years ago

Last date not blank

Hello  I have this table : Date  Sales 20/05/2023 400 27/10/2023  500 and I have a slicer that I can slice the data by year/quarter/date My goal is when i select for example the first quarter...
Need Help
AllisonKennedy's avatar
AllisonKennedy
Icon for Community Champion rankCommunity Champion
2 years ago

Anonymous What's the rationale for why you want to see 400 even when the 20/5/2023 date is not included in the selected period? We need a specific rule that we can translate to Power BI in order for this to work.

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    For this particular table I should receive data once every few months. That's why I want to spread the data and so I will see the last value that received even if I selected a month/quarter in which I did not received data.

    • AllisonKennedy's avatar
      AllisonKennedy
      Icon for Community Champion rankCommunity Champion
      2 years ago

      Anonymous I don't use this function often, but it may work here: LASTNONBLANKVALUE - DAX Guide

       

      You'll need to filter for date, so maybe something like this might work: 

      [LastValue] = 

      VAR _dateSlicer = MAX( dimDate[Date] )

      RETURN

      CALCULATE( 
      LASTNONBLANKVALUE ( dimDate[Date], factTable[Sales] ), 

      FILTER( ALL( dimDate ),

      dimDate[Date] <= _dateSlicer

      )

      )