I created a pivot table with a bunch of columns. Column A is a date, and Column B is Sales. I placed date in the filter. So I can select a date and filter my output.
I want to use DAX to find the number of days in the month selected in the filter, to calculate avg sales.
I can't figure out how to reference the filter in the EOMONTH("var",0). If it's plain excel, I would just refence A3 and get an answer.
Ideas?
Thanks
If you have SELECTEDVALUE available you can use that. Otherwise, you can simply refer to VALUES(DateField) - you'll probably want to wrap that in a HASONEVALUE test to make sure one particular date has been selected - to get the selected date.
Rory
Thanks Rory,
I tried all three options you gave. The results for SELECTEDVALUE and VALUES was a date vs number of days as in EOMONTH("var",0), so if I select a Febuary date, I get usually 28, but if January I'd get 31.
HASONEVALUE gave me "TRUE".
However, as a workaround, I just used DAY(EOMONTH("var",0) in the underlying sheet (new column).
Last edited by wspivak; 05-11-2023 at 07:06 AM.
You'd use:
DAY(EOMONTH(VALUES(Datefield),0))
in the DAX measure
Re: Pivot Filter as variable in DAX formula
That worked, Thanks!!!!!!!!!
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks