Dynamic variable calendar for column chart

[PowerBI_Canuck]

In my report I have a calendar slicer set to "Before".

The use case is, users will select the "to date" of the slicer and the report will display data relative to the selected period.

The selected period is a variable range on 2 ERP systems.

Ei 1:

selecting to date Dec 23 2020

ERP 1 start date: Jan 1 2020,

ERP 1 end date: Dec 31 2020

Calendar table from Jan 1 2020 to Dec 23 2020. The chart should be in this range.

Ei 2:

selecting to date March 12 2021

ERP 2 start date: Sept 10 2020

ERP 2 end date: August 19 2021

Calendar table from Sept 10 2020 to March 12 2021. The chart should be in this range.

selecting to date is "As of Date", what were my sales at. Users do not want to select a from date. In the furute there will be other ERPs added by region.

For measures I'm able to compute the above. My calendar table has the period start and end of each date

The challenge is with bar charts, the date filed is on the column axis.
No mater which date I select, the bar chart date axis will always be the earliest date in my date which is March 1st 2019.

In DAX, is it possible to have a dynamic date table? End date being end of the ERP period and start date the start of ERP start date.

I know the Calendar function but I'm not able to set the Start and end date.

IN the report, each ERP will be on seperate pages, never be combined.

Simple version of the model.

Thank you.

[amitchandak]

@PowerBI_Canuck , Not very clear. See if something like this can work

measure =
var _min = minx(allselected('ERP Table'), 'ERP Table'[Date])
var _max = maxx(allselected('Date'), 'Date'[Date])
return
calculate(Sum(Table[Date]), filter( Table, Table[Date] >=_min && Table[Date] <=_max))

[PowerBI_Canuck]

@AllisonKennedy I updated the description. The date table needs to be dynamic in DAX depending on the selected date. 

[AllisonKennedy]

@PowerBI_Canuck  It is not possible for a DAX table to update based on a slicer selection, only measures are calcualted after the slicer selection Power BI Order of Operations 

You don't need your Calendar table to change range though, only the visual, which happens automatically if you set the slicer to date 'before'. 

If I'm understanding you correctly, I think the only piece you're missing is that you want to also limit the start/from date. It sounds like you want the start date to equal the first date of the ERP period. So, add a column to your Date table (again I suggest you create this in Power Query) that is called Calendar[ERPperiod] and then you can add a filter to your measures that defines

VAR _erp = MAX(Calendar[ERPperiod]) 

RETURN

CALCULATE([measure], Calendar[ERPperiod] = _erp)

and that will limit the start date. There's other ways to do this too using an approximate lookup and basing on start of ERP date, etc. but this one should work.

[AllisonKennedy]

@PowerBI_Canuck  I don't fully understand your scenario yet as the Ei 1 and Ei 2 examples you have provided have a date selected in the middle of the range provided. What do you want your date table to look like in the end? 

I prefer to create the Date table in Power Query. Then you can add a conditional column for the ERP start and end dates, and use those in your slicer if I'm understanding your problem correctly? 

https://excelwithallison.blogspot.com/2020/04/dimdate-what-why-and-how.html