Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.

Reply
sw123
Helper III
Helper III

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 today in Power BI?

 

Thanx in advance!

1 ACCEPTED SOLUTION

hi @sw123 

supposing your table looks like:

FreemanZ_0-1678106299495.png

 

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:

FreemanZ_1-1678106388344.png

FreemanZ_2-1678106415713.png

 

View solution in original post

9 REPLIES 9
FreemanZ
Super User
Super User

hi @sw123 

what do you mean by "show"? How does your expected result look like?

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).

hi @sw123 

supposing your table looks like:

FreemanZ_0-1678106299495.png

 

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:

FreemanZ_1-1678106388344.png

FreemanZ_2-1678106415713.png

 

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:

sw123_1-1678110223656.png

 

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?

 

 

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
)

 

FreemanZ_0-1678111520577.png

 

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
)

Hi,

I am returning to this. Can anyone help me with what is wrong with ny measure and why it doesn´t return anything?

rajendraongole1
Post Prodigy
Post Prodigy

Hi @sw123 

Just create a new measure [Today Sales] as below:

 

Today Sales = CALCULATE([Total Sales],FILTER(Table1,Table1[Date]=TODAY()))

 

Try above and let know if it solves or not

Hi, there are two dates in the table, one beginning date and one ending date. And I want to show the prices that are valid today (today is on or between beginning date and end date). Can you still help me out?

Helpful resources

Announcements
Microsoft Fabric Learn Together

Microsoft Fabric Learn Together

Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City

PBI_APRIL_CAROUSEL1

Power BI Monthly Update - April 2024

Check out the April 2024 Power BI update to learn about new features.

April Fabric Community Update

Fabric Community Update - April 2024

Find out what's new and trending in the Fabric Community.