Different parts with price validity periods and search for special date

[Anonymous]

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!

[MFelix · Accepted solution]

Hi @Anonymous,

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

[Anonymous · Accepted solution]

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

I have made a shorter example of my data.

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

[TOK]

I've found a German tutorial in relation to this topic. I hope you still get along with it.

[Anonymous · Accepted solution]

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

I have made a shorter example of my data.

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

[MFelix · Accepted solution]

Hi @Anonymous,

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.

Check atach the PBIX file with the example.

Regards,

MFelix

[Anonymous]

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

[MFelix]

Hi @Anonymous,

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

[MFelix · Accepted solution]

Hi @Anonymous,

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

[AlB]

Hi @Anonymous

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