Forum Discussion
Generate a table dinamically based on a date slicer max value
Hi there,
I'm new with Power BI and I'm trying to recreate a dashboard I developed in Qlikview.
There is a dynamic table I generate based on the maximum value of a date range which is selected by the user and a predefined number of days.
For instance:
1) If a user selects 22/09/2018 - 24/09/2018 the table is calculated on the fly starting from the max value which is 24/09/2018.
2) If a user selects 24/09/2018 - 24/09/2018 the table is calculated on the fly starting from the max value which is 24/09/2018.
Based on any of the above cases and having a predefined number of days (3 for the current example), the table generated should be the one below.
| 24/09/2018 | 25/09/2018 | 26/09/2018 | 27/09/2018 | |
| Mon | Tue | Wed | Thu | |
| Arrivals | 18 | 6 | 14 | 6 |
| Departures | 25 | 14 | 23 | 2 |
In Qlikview I have the following:
Dimensions:
* TransactDate
* DayOfTheWeek
Metrics:
Arrivals set analysis ->
SUM( {< TransactDate= {">=$(=DATE(uiCalendarEndDate))<=$(=DATE(uiCalendarEndDate+3))"} >} #Arrivals)
Departures set analysis ->
SUM( {< TransactDate= {">=$(=DATE(uiCalendarEndDate))<=$(=DATE(uiCalendarEndDate+3))"} >} #Departures)
Note : uiCalendarEndDate is a variable which stores the max date.
I hope someone could help me or at least provide me any hint to accomplish something like this in Power BI.
Thanks in advance!
Best regards,
Diego
2 Replies
- v-danhe-msft
Microsoft Employee
Hi digichap28,
Could you please offer me some sample data to have a test or share the pbix file if possible?
Regards,
Daniel He
- digichap28New Member
Hi Daniel (v-danhe-msft),
I cant unfortunatly provide you the pbxi file but I prepared the following sample.
Fact Table %IdDate TransactDate DayOfTheWeek 43358 15/09/2018 Sat 43359 16/09/2018 Sun 43360 17/09/2018 Mon 43361 18/09/2018 Tue 43362 19/09/2018 Wed 43363 20/09/2018 Thu 43364 21/09/2018 Fri 43365 22/09/2018 Sat 43366 23/09/2018 Sun 43367 24/09/2018 Mon 43368 25/09/2018 Tue 43369 26/09/2018 Wed 43370 27/09/2018 Thu 43371 28/09/2018 Fri 43372 29/09/2018 Sat Date Catalog %IdDate #Arrivals #Departures 43358 15 7 43359 4 16 43360 8 8 43361 18 25 43362 6 14 43363 14 23 43364 6 2 43365 6 17 43366 30 20 43367 14 17 43368 13 9 43369 8 7 43370 19 6 43371 17 13 43372 10 12 I hope it works.
Best regards.