Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
3 years ago

Find value in selected date range

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

 

https://drive.google.com/file/d/1JPi7d8ico4m6flDDrL_UzM0nQAs9L2eG/view?usp=share_link 

https://docs.google.com/spreadsheets/d/1xkGzlGhnu06Ak5iX-pJ3L7mjOqGPu6yQ/edit?usp=share_link&ouid=112972022109944231932&rtpof=true&sd=true 

Need Help

2 Replies

  • mangaus1111's avatar
    mangaus1111
    Solution Sage
    3 years ago

    Hi Anonymous ,

    try this measure maybe it works:

    PricePerStartOfMonth =
    VAR c_date = SELECTEDVALUE ( 'Date'[Date] )
    VAR MinDate= MIN('Date'[Date])
    RETURN
        CALCULATE (
            MAXX (
                  FILTER(
                         'Historic_Gross_Price',
                          'Historic_Gross_Price'[Valid from] >= MinDate
                          ),
                'Historic_Gross_Price'[Price]
            ),
            c_date >= 'Historic_Gross_Price'[Valid from],
            c_date <= 'Historic_Gross_Price'[Valid to]
        )
     

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

    • Anonymous's avatar
      Anonymous
      Not applicable
      3 years ago

      Hi mangaus1111 

       

      Thanks a lot for your reply.

       

      Unfortunately, it didn't work...