PowerPivot dynamic date calculation

Hi all,

I'm trying to get into PowerPivot and the DAX formulas. So far I'm doing ok, but I can't figure out how to deal with dates properly.

So, for one explicit calculation I'm using

Formula:

Please Login or Register  to view this content.

and this is giving me my 50 open orders out of 200 total. When I add this to a PivotTable with Product name in the rows and the calculated field in the Values it works just fine, showing me only the 50 rows.

Although, to the above, I want to add a date field for current year.
If I use

Formula:

Please Login or Register  to view this content.

it works just fine, but I would like to have something like "current year" instead of text 2018 so this could be dynamic and I don't need to amend all this next year...

The closest I got was

Formula:

Please Login or Register  to view this content.

With this the calculation gives the same result (50) but when added to the pivot all the 200 entries are displayed.

Is there any date formula that can actually "filter" the dates when added to a pivot?

Thank you!

Originally Posted by ben_hensel

Shouldn't that last term be in CALCULATE() be:

Please Login or Register  to view this content.

EDIT:
Wait looks like you tried that too.

Well, since
"2018" <> 2018
(because even if their value is the same, a text string "2018" is not considered the same thing as the number 2018, so they are not the same when compared in a Boolean),

maybe try:
TEXT(YEAR(TODAY), "#")

Hi Ben

Using the date break down column with

Formula:

Please Login or Register  to view this content.

it just gives an error

Originally Posted by ben_hensel

Shouldn't that last term be in CALCULATE() be:

Please Login or Register  to view this content.

EDIT:
Wait looks like you tried that too.

Well, since
"2018" <> 2018
(because even if their value is the same, a text string "2018" is not considered the same thing as the number 2018, so they are not the same when compared in a Boolean),

maybe try:

Please Login or Register  to view this content.

I'm afraid TEXT is not accepted in DAX. It's not recognized as valid.

just thought

did you try :
Tracker[received date (Year)]=2018

Hey! Don't know how it works, but it does!

Thank you for your expertise and time. Cheers!

Originally Posted by ben_hensel

Guh maybe forcing it into a text string with a stupid concatenation then:

Please Login or Register  to view this content.

Hey! Don't know how it works, but it does!

Thank you for your expertise and time. Cheers!