Forum Discussion

kincaids's avatar
kincaids
Helper II
9 years ago

Seemingly simple date issue...

My company rents band & orchestra instruments to school children.  I have a handful of data query tables and I've created a calendar table.

 

For this issue, I think all I need to focus on are these two tables:

  • 'Calendar'
  • 'Rental Accounts - scrental'

 

I created a new column (as opposed to a new measure) in the rental accounts table for customer rentals with this formula:

 

Current Rentals = CALCULATE(COUNTROWS('Rental Accounts - scrental'),'Rental Accounts - scrental'[Return Date]=BLANK(),'Rental Accounts - scrental'[rnt_dunn]="P" || 'Rental Accounts - scrental'[rnt_dunn]="N")

  

which results in a simple card:

Fig. 1

 

I have multiple date columns within the rental accounts table (posting date, contract date, etc.).  Using the contract date, I can see how many rentals we began in a given year against the current rentals (ones that are still out being rented, from above) like this:

Fig. 2

 

What I want and can't seem to be able to do is create a line chart much like the Dow Jones line chart.  What was the dow at yesterday?  last year? ten years ago?  I simply want to keep a "running line chart" of the total number of current rentals.  In other words, today, as you can see, there are 2,438.  If two are returned today, tomorrow the total would be 2,436.  X-axis would be a generic date (which is where I think the 'Calendar' table I created comes in) and the Y-axis would simply be the number of current rentals.

 

When I try this, all I get is this:

Fig. 3

 

I have a feeling there is an issue with the relationships.  However, when I try to create a 1:* relationship between the 'Calendar' table (Date) and the 'Rental Account - scrental' table (Contract Date, for instance), it screws up the table above it, removing the heirarchy from it and makes the table I'm trying to create into, essentially, the same thing as the what the table above it was originally:

Fig. 4

 

I'm feeling pretty stupid here.  I've been searching for date help all over the place, but getting nowhere.  Any help would be appreciated!

 

18 Replies

  • v-huizhn-msft's avatar
    v-huizhn-msft
    Microsoft Employee

    Hi kincaids,

    In the Fig3, the line in “Current Rentals by Year” chart is flat, which is caused by the current Rentals measure result in each year is same. How do you get the Current Rentals chart in Fig3? The orange line look normal.

    For your Fig4, you select the contract date as Axis,  the contract date column only including year in resurce data, right? If it is, please try to add filter, and check if it works fine.

     

    Current Rentals = CALCULATE(COUNTROWS('Rental Accounts - scrental'),'Rental Accounts - scrental'[Return Date]=BLANK(),'Rental Accounts - scrental'[rnt_dunn]="P" || 'Rental Accounts - scrental'[rnt_dunn]="N",Filter(Rental Accounts - scrental,Rental Accounts - scrental[contract date]<=MAX(Calendar[Date])))

     

    Best Regards,
    Angelia

    • kincaids's avatar
      kincaids
      Helper II

      v-huizhn-msft

       

      I added names for each picture in my original post.  Could you edit your previous post to reflect the image names and then post a short reply so I know you've done that?  Thanks!  I'm having a hard time following your though process...

      • v-huizhn-msft's avatar
        v-huizhn-msft
        Microsoft Employee

        Hi kincaids,

        Sorrr for inconvenience, I update the reply, please verify and feel free to ask if you have any question.

        Best Regards,
        Angelia