Remove future dates in DAX expression for running total

[Anonymous]

Hi,

Ideally I would like to adjust the DAX expression so that future dates from my running total (quick measure) calculation do not appear in the graph.  Currenty they appear as a horizontal line.  I have used quick measure to generate the following code:

Count of CSR_NO running total in Month =
   CALCULATE(
      COUNTA('MY_DATA'[CSR_NO]),
        FILTER(
            CALCULATETABLE(
                SUMMARIZE(
                     'MY_DATA', 
                      'MY_DATA'[CSR_CREATE_DATE].[MonthNo],
                       'MY_DATA'[CSR_CREATE_DATE].[Month]
                 ),
           ALLSELECTED('MY_DATA')
          ),
      ISONORAFTER(
         'MY_DATA'[CSR_CREATE_DATE].[MonthNo], MAX('MY_DATA'[CSR_CREATE_DATE].[MonthNo]), DESC,
          'MY_DATA'[CSR_CREATE_DATE].[Month], MAX('MY_DATA'[CSR_CREATE_DATE].[Month]), DESC
      )
   )
)

I am aware this topic has come up a few times but the suggestions to add IF(ISBLANK(CSR_NO), BLANK(), CALCULATE(etc)

did not work.

How may I filter out future dates (from TODAY()) ?

Any help would be much appreciated.

Thank you,

Phil,

[v-xicai · Accepted solution]

Hi @Anonymous  ,

Does that make sense? If so, kindly mark my answer as a solution to help others having the similar issue and close the case. If not, let me know and I'll try to help you further.

Best regards

Amy

[v-xicai]

Hi @Anonymous ,

You may create measure like DAX below.

Measure1=IF(MAX( 'MY_DATA'[CSR_CREATE_DATE])>TODAY(),BLANK(), [Count of CSR_NO running total in Month])

If I misunderstood it, could you please share your sample data and desired output screenshots for further analysis? You can also upload sample pbix to OneDrive and post the link here. Do mask sensitive data before uploading.

Best Regards,

Amy

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.