Use date from and date to as axis on line chart

[debiesven]

I have a status table with the following 5 fields:

• Article number
• Stock status (in stock, limited in stock, out of stock)
• Date from
• Date to
• Is current

Based on these field I can manually check each moment in time that a product was in stock, limited in stock or out of stock. But now I want to put it into a line graph. So on 07/03/2022 we have 500 products in stock, 25 products limited in stock and 10 products out of stock. On day 2 (08/03/2022) we have 490 products in stock, 30 products limited in stock and 15 products out of stock. But I only have a from and a to date. Not the status date of 07/03/2022 or 08/03/2022 to use as the x-axis, but only the full period based on the from and to dates.

What is the best way to define what the status was on a particular day so it can easily be shown in a chart or graph.

[Ashish_Mathur]

Hi,

What is the purpose/use of the "Is Current" column?  Share data in a format that can be pasted in an MS Excel workbook.

[debiesven]

So create a simple flag for the latest results. It can be possible that a record has been deleted, so the date to is yesterday. But it is still the latest version, so the is current = true.

[AllisonKennedy]

There might be more efficient ways, but you could use a DAX approximate lookup with an unrelated date table: 

https://excelwithallison.blogspot.com/2020/06/dax-approximate-lookup.html

https://excelwithallison.blogspot.com/2021/09/power-bi-forecasting-with-irregular.html 

[debiesven]

I don't understand the explanation how to convert these periods into a daily state based on date. 

[Anonymous]

Hi @debiesven,

Did this flag field manually add on the data source side?

If that is the case, you can create a calculated column to compare with the date field with Dax today function and set a flag based on the result, these function results will be re-calculated every time the system date changes.

Flag =
IF ( Table[Date] = TODAY (), "Today" )

TODAY function (DAX) - DAX | Microsoft Docs
Regards,

Xiaoxin Sheng

[debiesven]

Hi,

Thanks for your reply. The Is current flag is generated by our data warehouse. So that one is okay. My question is only about mapping the period to a certain date so I can plot it on a historical graph.

[Anonymous]

Hi @debiesven,

>>My question is only about mapping the period to a certain date so I can plot it on a historical graph.

It sounds like a common requirement about date value mapping to the date range defined by multiple fields. For this scenario you can refer to the following blog 'start date', 'end date' part or try to create a bridge table that expands the date range to map to the detailed date records:

Before You Post, Read This 

Solved: Spread revenue across period based on start and en... - Microsoft Power BI Community

Regards,

Xiaoxin Sheng