Products with multiple 'valid from' and 'valid to' in one calendar year

DAX DAX Showcase
1

Example v1.pbix (420.6 KB)
Example Data.xlsx (11.4 KB)

Hi

I have a number of products which has customer agreed pricing with ‘Valid From’ date and ‘Valid To (expires)’ date.

When the price expires, the new price becomes valid the day after.

These prices cover multiple customers and don’t all expire at the same time.

In a table, how do I show a new price automatically starts the day after the old price expires?

This table will then be exported into CSV for emailing to customer. The raw data is also being used for other reports in Power BI, which is not included in my upload.

Example raw data and .pbix uploaded

2

Hi @AlistairNicoll ,

Welcome to Forum. Thank you for your interesting question.

First you need to set up the base from 2023

image

then Merge with 2024 (Merge2 query)
add new Valid from using formula

Date.AddDays([Valid To],1)

Then Append with 2023 (Append2 query)

and make Result Valid From, Valid To, Price (combining 2023 & 2024)

Pbix Example

Example v2.pbix (437.2 KB)

Hope this is what are you looking for.