cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Frequent Visitor

Different parts with price validity periods and search for special date

I am new to Power BI and so I have come to the point I desperately need help.

 

I have data with lots of different parts and their prices. All of them are valid over a specific time period. How can I search for a specific date and get all of the prices which are/were valid on this particular date?

 

For instance:

Produkt A: Price valid from 01.01.2017 till 01.01.2019

Produkt B: Price valid from 05.12.2018 till 20.01.2020

Produkt C: Price valid from 05.10.2018 till 20.01.2019

Produkt 😧 Price valid from 05.02.2018 till 20.01.2018

 

Now I want all prices which are valid on the 10.12.2018.

 

Thank You!

3 ACCEPTED SOLUTIONS
Super User III
Super User III

Hi @astecker7,

 

I'm assuming that the dates in the products validity are in two separate columns something like this:

 

Product    Starting Date             End Date

A 01 January 2017 01 January 2019
B 05 December 2018 20 January 2020
C 05 October 2018 20 January 2019
D 05 February 2018 20 January 2018

 

 

Create a calendar table that as the full dates in your range then used it has you slicer (don't make any relationship with the other tables). Create the following measure to filter your visual:

Active Products =
IF (
    MAX ( Products[Starting Date] ) > SELECTEDVALUE ( 'Calendar'[Date] )
        || MAX ( Products[End Date] ) < SELECTEDVALUE ( 'Calendar'[Date] );
    0;
    1
)

 

Be aware that this formula only allow 1 date selection at a time with more than one day selected you will get an empty table.

 

Check PBIX file attach.

 

Regards,

MFelix


Regards

Miguel Félix


Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

Check out my blog:

Power BI em Português





View solution in original post

Frequent Visitor

Thank you for your answeres, but it is still unclear how to solve it.

 

I have made a shorter example of my data.

Table.JPG

This is the table of my example.

 

You can see the price of PART A  is only VALID if it is purchased between the September 1 of 2017 and December 31 0f 2018.

PART N's price is valid if its purchased between February 2 of 2018 and January 1 of 2019. The same for all the others.

 

Now, if I am searching for Date X, for instance 12.06.2018, I should get all Parts in which timespan the date is set.

In tis case: A,B,J,K,L,M

 

Thanks in Advance,

 

Sebastian

View solution in original post

Hi @astecker7,

 

As refered you need to create a calendar table then add the measure I refer and used it on your visuals to filter out the inactive products.

 

Just one thing looking at your data shouldn' the information retrive be:

 

A, B, J, K  L, M, N,  O , P?

 

N, O  and P are also active on the 12 of July 2018.

active.png

 

Check atach the PBIX file with the example.

 

Regards,

MFelix


Regards

Miguel Félix


Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

Check out my blog:

Power BI em Português





View solution in original post

6 REPLIES 6
Frequent Visitor

Thank you for your answeres, but it is still unclear how to solve it.

 

I have made a shorter example of my data.

Table.JPG

This is the table of my example.

 

You can see the price of PART A  is only VALID if it is purchased between the September 1 of 2017 and December 31 0f 2018.

PART N's price is valid if its purchased between February 2 of 2018 and January 1 of 2019. The same for all the others.

 

Now, if I am searching for Date X, for instance 12.06.2018, I should get all Parts in which timespan the date is set.

In tis case: A,B,J,K,L,M

 

Thanks in Advance,

 

Sebastian

View solution in original post

Hi @astecker7,

 

As refered you need to create a calendar table then add the measure I refer and used it on your visuals to filter out the inactive products.

 

Just one thing looking at your data shouldn' the information retrive be:

 

A, B, J, K  L, M, N,  O , P?

 

N, O  and P are also active on the 12 of July 2018.

active.png

 

Check atach the PBIX file with the example.

 

Regards,

MFelix


Regards

Miguel Félix


Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

Check out my blog:

Power BI em Português





View solution in original post

Is there a possibility to get the Sum of all the prices at a specific day?

 

Hi @astecker7,

 

Can you please ellaborate on what do you mean by sum of prices in a specif day? do you want to place it on card visual a table?

 

Regards,

MFelix


Regards

Miguel Félix


Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

Check out my blog:

Power BI em Português





Super User III
Super User III

Hi @astecker7,

 

I'm assuming that the dates in the products validity are in two separate columns something like this:

 

Product    Starting Date             End Date

A 01 January 2017 01 January 2019
B 05 December 2018 20 January 2020
C 05 October 2018 20 January 2019
D 05 February 2018 20 January 2018

 

 

Create a calendar table that as the full dates in your range then used it has you slicer (don't make any relationship with the other tables). Create the following measure to filter your visual:

Active Products =
IF (
    MAX ( Products[Starting Date] ) > SELECTEDVALUE ( 'Calendar'[Date] )
        || MAX ( Products[End Date] ) < SELECTEDVALUE ( 'Calendar'[Date] );
    0;
    1
)

 

Be aware that this formula only allow 1 date selection at a time with more than one day selected you will get an empty table.

 

Check PBIX file attach.

 

Regards,

MFelix


Regards

Miguel Félix


Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

Check out my blog:

Power BI em Português





View solution in original post

Super User III
Super User III

Hi @astecker7

Welcome to the community.

Can you show the tables in your data model involved in the question?  It would also help a lot if you give us an example based on that sample data to explain what you are looking for. It's not completely clear right now

 

Please always show your sample data in text-tabular format in addition to (or instead of) the screen captures. That allows people trying to help to readily copy the data and increases the likelihood of your question being answered. Just use 'Copy table' in Power BI Desktop and paste it here. Or ideally, share the pbix (beware of confidential data in it).

 

Cheers

Helpful resources

Announcements
secondImage

Congratulations!

We are excited to announce the Power BI Super Users!

Wave Release 2

Check out the updates in Power BI.

Overview of Power BI 2020 release wave 2!

Microsoft Ignite

Microsoft Ignite

Join digitally, March 2–4, 2021 to explore new tech that's ready to implement. Experience the keynote in mixed reality through AltspaceVR!

secondImage

The largest Power BI virtual conference

100+ sessions, 100+ speakers, Product managers, MVPs, and experts. All about Power BI. Attend online or watch the recordings.

Top Solution Authors
Top Kudoed Authors