Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register Now

Reply
PowerBI_Canuck
Frequent Visitor

Dynamic variable calendar for column chart

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

2020-12-30_14-18-48.png

 

 

 

 

 

 

 

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.

2020-12-30_14-24-01.png

 

 

 

 

 

 

 

 

 

 

Thank you.

4 REPLIES 4
amitchandak
Super User
Super User

@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
Frequent Visitor

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

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


Please @mention me in your reply if you want a response.

Copying DAX from this post? Click here for a hack to quickly replace it with your own table names

Has this post solved your problem? Please Accept as Solution so that others can find it quickly and to let the community know your problem has been solved.
If you found this post helpful, please give Kudos C

I work as a Microsoft trainer and consultant, specialising in Power BI and Power Query.
www.excelwithallison.com

AllisonKennedy
Super User
Super User

@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 


Please @mention me in your reply if you want a response.

Copying DAX from this post? Click here for a hack to quickly replace it with your own table names

Has this post solved your problem? Please Accept as Solution so that others can find it quickly and to let the community know your problem has been solved.
If you found this post helpful, please give Kudos C

I work as a Microsoft trainer and consultant, specialising in Power BI and Power Query.
www.excelwithallison.com

Helpful resources

Announcements
Fabric Community Conference

Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

February 2024 Update Carousel

Power BI Monthly Update - February 2024

Check out the February 2024 Power BI update to learn about new features.

Fabric Career Hub

Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

Fabric Partner Community

Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.