Forum Discussion

pantherfanrh's avatar
pantherfanrh
Frequent Visitor
9 years ago
Solved

Graph values between dates?

I have two values, open, and close date. I would like to show a graph showing how many stores were open at that certain date. So something like the x axis would be dates, and the y axis would be how ...
Need Help
  • v-qiuyu-msft's avatar
    v-qiuyu-msft
    9 years ago

    Greg_Deckler Great idea. :smileyhappy:

     

    pantherfanrh I would like to make Greg_Deckler's suggestion more perfect. Assume the end date is blank, means the store still open until now. So we can create a measure like this:

     

    Measure = CALCULATE(COUNTA(Table1[Store]),FILTER(ALL(Table1),'Table1'[Open]<=MAX('Calendar'[Date]) && ('Table1'[Close]>MAX('Calendar'[Date])|| 'Table1'[Close]=BLANK())))

     

     

    Please download attached .pbix file to have a look.

     

    Best Regards,
    Qiuyun Yu

v-qiuyu-msft's avatar
v-qiuyu-msft
Community Support
9 years ago

Greg_Deckler Great idea. :smileyhappy:

 

pantherfanrh I would like to make Greg_Deckler's suggestion more perfect. Assume the end date is blank, means the store still open until now. So we can create a measure like this:

 

Measure = CALCULATE(COUNTA(Table1[Store]),FILTER(ALL(Table1),'Table1'[Open]<=MAX('Calendar'[Date]) && ('Table1'[Close]>MAX('Calendar'[Date])|| 'Table1'[Close]=BLANK())))

 

 

Please download attached .pbix file to have a look.

 

Best Regards,
Qiuyun Yu

  • pantherfanrh's avatar
    pantherfanrh
    Frequent Visitor
    9 years ago

    This was so helpful! It worked perfectly. Thank you so much!