Yingying, a couple of us have looked at this and we're really not sure what you're trying to do.
I have tried to do this if I am using now() or today() and it works,
In the workbook you attached I am not seeing any "working" % difference. Please add a sheet to the workbook showing this working, then maybe we'll better understand what you're trying to accomplish. Thanks,
I don't want to confuse you. using today() or now() is NOT working. using today() or now() can only give me % different between a fixed day and the days in previous months. What I need to do is; the user needs to be able to control the date they want to compare, say if they pick 8/6, they can see % different of sum(8/1-8/6) vs. sum(7/1-7/6). if the users pick 8/5, they can see % different of sum(8/1-8/5) vs. sum(7/1-7/5).
as the data will be updated each day, so I can't use parameter as parameter wont' refresh even it has one more day's data in the workbook.
Yingying, it sounds like you are really looking for a dynamic parameter, which is the #1 feature request in the ideas section. So that's not an option. However if you can live with your user typing in the Maximum Day of the Month as an integer, then you can get the % difference you're looking for (see attached). Here's the calculation:
IF DATEPART('day', [date] )<=[Max Day of Month] THEN [KPI] END
Hopefully this will work for you.
EDIT: That was the wrong workbook. I've attached the correct one now.
Month to date-SW.twbx.zip 13.9 KB
Thanks Shawn, This will fix the problem somehow. however, the users always get confused as they don't know how to set up the max. date as sometimes the workbook failed to update, the max. date is not necessary to be yesterday.