Forum Discussion

CROforce's avatar
CROforce
Helper I
9 years ago
Solved

Count by Date using filters

Here is my sample data table called “observation” :   I have also created new Table called “Calendar” in Query editor which contains Date that goes from 9/30/2015 until today and I also have ...
Need Help
  • dkay84_PowerBI's avatar
    dkay84_PowerBI
    9 years ago

    Rather than custom formulas, have you tried the "Group By" funcitonality  within the query editor?

dkay84_PowerBI's avatar
dkay84_PowerBI
Microsoft Employee
9 years ago

Rather than custom formulas, have you tried the "Group By" funcitonality  within the query editor?

CROforce's avatar
CROforce
Helper I
9 years ago

That actually worked (the filtering part)... Thank you for the help. However, when I try to create Measure to calculate "Net Open" over time period to show on the Chart, it's not giving me right number based on filters selected:

 

Here is my table (gruped by) and I have also addedd Index to this to start from 1:

 

Then I have created Messure to count Net Open overtime using the same formula as in my previous attempt which worked (but I couldn't figureouit Gating Milestone filtering):

Now calculations here are not right as you can see numbers do not match:

 

And if I select any filter from the Gating Milestone, the numbers would be totally wrong :-(

 

This is what the chart should look like (blue line)

 

  • dkay84_PowerBI's avatar
    dkay84_PowerBI
    Microsoft Employee
    9 years ago
    Can you post some sample data or the PBIX for us to play with?
    • CROforce's avatar
      CROforce
      Helper I
      9 years ago

      I have just figured it out... I had to use ALLSELECTED in my formula instead of ALL

      Net OPEN = 
    CALCULATE (
        SUM ( 'Open'[Open] ) ,
        FILTER ( ALLSELECTED ( 'Open' ), 'Open'[Index] <= MAX( ( 'Open'[Index] ) )
    ))