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 of 2023 I should see 400 and when i select the first quarter of 2024 or the all year, I will see the 500.
If I select the third quarter of 2023 or Month 9 in 2023 I will see 400.
I slice the data by dim_date table and the sales in a fact table.
Thank you!
Thank you.

Need Help

3 Replies

  • 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

        )

        )