Forum Discussion

digichap28's avatar
digichap28
New Member
8 years ago

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/201825/09/201826/09/201827/09/2018
MonTueWedThu
Arrivals186146
Departures2514232

 

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's avatar
    v-danhe-msft
    Icon for Microsoft Employee rankMicrosoft 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

    • digichap28's avatar
      digichap28
      New Member

      Hi Daniel (),

       

      I cant unfortunatly provide you the pbxi file but I prepared the following sample.

       

      Fact Table
      %IdDateTransactDateDayOfTheWeek
      4335815/09/2018Sat
      4335916/09/2018Sun
      4336017/09/2018Mon
      4336118/09/2018Tue
      4336219/09/2018Wed
      4336320/09/2018Thu
      4336421/09/2018Fri
      4336522/09/2018Sat
      4336623/09/2018Sun
      4336724/09/2018Mon
      4336825/09/2018Tue
      4336926/09/2018Wed
      4337027/09/2018Thu
      4337128/09/2018Fri
      4337229/09/2018Sat

       

      Date Catalog
      %IdDate#Arrivals#Departures
      43358157
      43359416
      4336088
      433611825
      43362614
      433631423
      4336462
      43365617
      433663020
      433671417
      43368139
      4336987
      43370196
      433711713
      433721012

       

       

      I hope it works.

       

       

      Best regards.