Forum Discussion

sw123's avatar
sw123
Helper III
3 years ago
Solved

Price valid today

Hi,   I have a pricelist-table with different pricelists. All pricelists contain prices for items and an start date and an end date for each price / item. How do I show only prices that are valid t...
Need Help
  • FreemanZ's avatar
    FreemanZ
    3 years ago

    hi sw123 

    supposing your table looks like:

     

    try plot a table visual with all the columns and feed a measure to the filter pane like:

    Measure = 
IF(
    MAX(TableName[startdate]) <= TODAY()
        &&MAX(TableName[enddate]) >= TODAY(),
    1,0
)

     

    it worked like:

     

sw123's avatar
sw123
Helper III
3 years ago

Hi, I want to create a table in Power BI, that shows a list of the items and the prices that are valid today (today is on or between the beginning date and end date).

FreemanZ's avatar
FreemanZ
Super User
3 years ago

hi sw123 

supposing your table looks like:

 

try plot a table visual with all the columns and feed a measure to the filter pane like:

Measure = 
IF(
    MAX(TableName[startdate]) <= TODAY()
        &&MAX(TableName[enddate]) >= TODAY(),
    1,0
)

 

it worked like:

 

  • sw123's avatar
    sw123
    Helper III
    3 years ago

    Yes, this works great. But it did display the next problem 😄

     

    We seem to have multiple prices in the price list for the same item, that seem to be valid today, but then actually only the one with the latest beginning date should show in the table.

     

    For example, when the table looks like this:

     

    It seems that all the prices are valid today, but should only show the ones with start date 3/1/2023. Can you help, with this issue also?

     

     

    • FreemanZ's avatar
      FreemanZ
      Super User
      3 years ago

      hi sw123 

      then try like:

      Measure = 
VAR _maxdate =
MAXX(
        ALL(TableName[startdate]),
        TableName[startdate]
)
RETURN
IF(
    MAX(TableName[startdate]) <= TODAY()
        &&MAX(TableName[startdate]) = _maxdate
        &&MAX(TableName[enddate]) >= TODAY(),
    1,0
)

       

       

      • sw123's avatar
        sw123
        Helper III
        3 years ago

        Hi,

         

        This doesn´t return anything for me with filter 1. Do you have any idea what could be wrong?

         

        Measure =
        VAR _maxdate =
        MAXX(
                ALL('Price list'[BegDate]),
                'Price list'[BegDate]
        )
        RETURN
        IF(
            MAX('Price list'[BegDate]) <= TODAY()
                &&MAX('Price list'[BegDate]) = _maxdate
                &&MAX('Price list'[EndDate]) >= TODAY(),
            1,0
        )