Show value from selected date range

  • Show value from selected date range

    Posted by DSC Communities on May 26, 2023 at 4:04 pm

    Show value from selected date rangeJump to Best AnswerFollow
    Helen Wrensted
    Helen WrenstedNov 01, 2022 07:10 AM
    Hi. I am trying to create a report showing the development in purchase prices over a given period. In …
    1. Show value from selected date range

    Bronze Contributor
    Helen Wrensted
    Posted Nov 01, 2022 07:10 AM | view attached (2)
    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)

    xlsxHistoric_Gross_Price.xlsx
    pbixCostPrices.pbix

    2. RE: Show value from selected date range

    Rd 3nidad
    Posted Nov 02, 2022 04:55 AM | 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)

    pbixCostPrices (1).pbix

    3. RE: Show value from selected date range

    Bronze Contributor
    Helen Wrensted
    Posted Nov 02, 2022 12:50 PM | 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)

    pbixCostPrices (1).pbix

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

    Rd 3nidad
    Posted Nov 02, 2022 03:25 PM
    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
    Helen Wrensted
    Posted Nov 06, 2022 07:38 AM
    It works perfectly – thanks a lot!

    ——————————
    Helen Wrensted
    Business Developer
    ——————————

     

    6. RE: Show value from selected date range

    Bronze Contributor
    Tomas Torp
    Posted Nov 04, 2022 07:25 PM | 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)

    pbixCostPrices 2.pbix

    7. RE: Show value from selected date range

    Bronze Contributor
    Helen Wrensted
    Posted Nov 06, 2022 08:44 AM
    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

    Kaz Shakir
    Posted Nov 05, 2022 04:51 PM
    Edited by Kaz Shakir Nov 05, 2022 05:12 PM | 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)

    pbixCostPrices_r3.pbix

    9. RE: Show value from selected date range

    Bronze Contributor
    Helen Wrensted
    Posted Nov 06, 2022 08:39 AM
    Hi Kaz.

    Thanks a lot for your solution – it works great!

    ——————————
    Helen Wrensted
    Business Developer
    ——————————

    DSC Communities replied 1 year ago 1 Member · 0 Replies
  • 0 Replies

Sorry, there were no replies found.

The discussion ‘Show value from selected date range’ is closed to new replies.

Start of Discussion
0 of 0 replies June 2018
Now

Welcome to our new site!

Here you will find a wealth of information created for people  that are on a mission to redefine business models with cloud techinologies, AI, automation, low code / no code applications, data, security & more to compete in the Acceleration Economy!