Power BI Exchange

Please login or click SIGN UP FOR FREE to create your PowerBIUG account to join this user group.
 View Only
  • 1.  Show value from selected date range

    Bronze Contributor
    Posted Nov 01, 2022 07:10 AM
    Hi.

    I am trying to create a report showing the development in purchase prices over a given period. In inclosed report, I have the purchase prices in one table and a date table.
    When I select a period (based on date table) from March 12 2021 to March 30 2022 I would like to see all puchase prices in this period. However, if I have a purchase price valid from January 1 2021 to April 30 2021 this price wont show in the report as the start date for the price is before the start date in the date table.

    How can I solve this? The price is valid on March 12...

    Pls. see enclosed file - I have used article "Test 4" in above description.

    Thanks a lot in advance.
    Helen

    ------------------------------
    Helen Wrensted
    Business Developer
    ------------------------------

    Attachment(s)

    xlsx
    Historic_Gross_Price.xlsx   11 KB 1 version
    pbix
    CostPrices.pbix   25.17 MB 1 version


  • 2.  RE: Show value from selected date range

    Posted 30 days ago
      |   view attached

    Hi Helen,

    Here's what you should do:

    a. Create another date table and connect to valid from column ---> this is just have your date always at the beginning of the month (you can use valid from date directly as well)
    b. Create inactive relationship between main table and existing date table (connection: Valid to and Date)
    c. Create a formula to incorporate the "valid to" values to your existing results (I removed the unnecessary formula btw).

    PricePerStartOfMonth =

    Var MaxDate = CALCULATE(MAX('Date'[Date]),ALLSELECTED('Date'[Date]))
    Var MinDate = CALCULATE(Min('Date'[Date]),ALLSELECTED('Date'[Date]))

    VAR ValidTo =     CALCULATE (
            MAX ( 'Historic_Gross_Price'[Price] ),
              USERELATIONSHIP(Historic_Gross_Price[Valid to],'Date'[Date]),
              MinDate>=Historic_Gross_Price[Valid from] && MinDate <=Historic_Gross_Price[Valid to])
             

    RETURN

            CALCULATE(MAX ( 'Historic_Gross_Price'[Price] ),'Date'[First day] = "Yes") +
             ValidTo



    ------------------------------
    Rd 3nidad
    Senior Finance Analyst
    ------------------------------

    Attachment(s)

    pbix
    CostPrices (1).pbix   76 KB 1 version


  • 3.  RE: Show value from selected date range

    Bronze Contributor
    Posted 30 days ago
      |   view attached
    Hi.

    Thanks a lot for your reply.

    It works great! However, I have a problem with the prices that are effective from dates that are not the first day of the month. In enclosed file, I have added a simple matrix where we would like to see all cost prices in selected period. For article Test 4 it is fine, but if you look at article Test 3 (where some of the prices are effective from mid month), prices are missing.

    How can this be soved?
    Thanks a lot in advance.

    ------------------------------
    Helen Wrensted
    Business Developer
    ------------------------------

    Attachment(s)

    pbix
    CostPrices (1).pbix   76 KB 1 version


  • 4.  RE: Show value from selected date range
    Best Answer

    Posted 30 days ago
    Hi Helen, In that case, just remove the filter context in the formula. 
    PricePerStartOfMonth =

    Var MaxDate = CALCULATE(MAX('Date'[Date]),ALLSELECTED('Date'[Date]))
    Var MinDate = CALCULATE(Min('Date'[Date]),ALLSELECTED('Date'[Date]))

    VAR ValidTo =     CALCULATE (
            MAX ( 'Historic_Gross_Price'[Price] ),
              USERELATIONSHIP(Historic_Gross_Price[Valid to],'Date'[Date]),
              MinDate>=Historic_Gross_Price[Valid from] && MinDate <=Historic_Gross_Price[Valid to])
             
    RETURN

            CALCULATE(MAX ( 'Historic_Gross_Price'[Price] )) +
             ValidTo


    ------------------------------
    Rd 3nidad
    Senior Finance Analyst
    ------------------------------



  • 5.  RE: Show value from selected date range

    Bronze Contributor
    Posted 26 days ago
    It works perfectly - thanks a lot!

    ------------------------------
    Helen Wrensted
    Business Developer
    ------------------------------



  • 6.  RE: Show value from selected date range

    Bronze Contributor
    Posted 27 days ago
      |   view attached
    From the title of the measure, "Price per start of month", I assume that you want the price to be displayed not on the actual valid from date, but at the first month start date on or after the valid from date. In the attached file I have set up a solution based on that assumption.

    ------------------------------
    Tomas
    ------------------------------

    Attachment(s)

    pbix
    CostPrices 2.pbix   60 KB 1 version


  • 7.  RE: Show value from selected date range

    Bronze Contributor
    Posted 26 days ago
    Hi Tomas.

    Thanks a lot for the solution, which works great for the first month start date.

    ------------------------------
    Helen Wrensted
    Business Developer
    ------------------------------



  • 8.  RE: Show value from selected date range

    Posted 27 days ago
    Edited by Kaz Shakir 27 days ago
      |   view attached
    @Helen Wrensted,
    I think that you should get the result that you want if you simply delete the relationship between your Date table and the Historic_Gross_Price table.  Then the measures you have created will give you the results that you are expecting.  So, your data model will simply look like this:

    And after you delete that relationship, your resulting matrix will look like this:
    Another approach would be to create a new historic gross price table, where the prices for each "Material" are shown for each date.  This new table will be a cross join between that dates of the date table and different material.  Here is the formula I used to create that table:
    Daily_Historic_Gross_Price = 
    CROSSJOIN(
        Calendar( Date(2017, 1, 1), Date(2022,3,30)),
        VALUES(Historic_Gross_Price[Material])
    )​
    ​Next, I would create calculated columns in this new Daily_Historic_Gross_Price table that show the price that's effective on that given date for that given material, and another to show the currency that is effective during that time as well.  Here are the formulas for those columns:
    Effective_Price = 
    VAR currentDate = 'Daily_Historic_Gross_Price'[Date]
    VAR currentMaterial = 'Daily_Historic_Gross_Price'[Material]
    VAR filteredData = 
        FILTER(
            Historic_Gross_Price,
            Historic_Gross_Price[Material] = currentMaterial && Historic_Gross_Price[Valid from] <= currentDate && Historic_Gross_Price[Valid to] >= currentDate
        )
    VAR relatedPrice = 
        MAXX(
            filteredData,
            Historic_Gross_Price[Price]
        )
    RETURN
        relatedPrice​
    Effective_Currency = 
    VAR currentDate = 'Daily_Historic_Gross_Price'[Date]
    VAR currentMaterial = 'Daily_Historic_Gross_Price'[Material]
    VAR filteredData = 
        FILTER(
            Historic_Gross_Price,
            Historic_Gross_Price[Material] = currentMaterial && Historic_Gross_Price[Valid from] <= currentDate && Historic_Gross_Price[Valid to] >= currentDate
        )
    VAR relatedCurrency = 
        MAXX(
            filteredData,
            Historic_Gross_Price[Currency]
        )
    RETURN
        relatedCurrency

    Then, in your data model, you would connect the Date table to this new Daily_Historic_Gross_Price table:
    Finally, in your table visualization, you would use the columns from the Date table and the Daily_Historic_Gross_Price table, so the result would look like this:

    I am attaching my solution here for your review.
    Please let us know if this gets you the results you were looking for.

    Kaz.



    ------------------------------
    Kaz Shakir
    Sr. Program Manager, Asset Planning
    TN
    ------------------------------

    Attachment(s)

    pbix
    CostPrices_r3.pbix   25.19 MB 1 version


  • 9.  RE: Show value from selected date range

    Bronze Contributor
    Posted 26 days ago
    Hi Kaz.

    Thanks a lot for your solution - it works great!

    ------------------------------
    Helen Wrensted
    Business Developer
    ------------------------------