Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

Remove future dates in DAX expression for running total

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,

 

  • 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

2 Replies

  • v-xicai's avatar
    v-xicai
    Icon for Community Support rankCommunity Support

    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.

     

  • v-xicai's avatar
    v-xicai
    Icon for Community Support rankCommunity Support

    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