Forum Discussion
Dynamic variable calendar for column chart
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.
4 Replies
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
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.
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))
