Max Year as single measure value to use as condition for other measures

[szczawek]

Hello,

I am stuck with figuring out what MayYear measure should be set in dax so i can recive a single value.

When i use dax for MaxYear as:

MaxYear = 
var Max_ID_Date= 
CALCULATE(
    MAX(Fact[ID_Data])
    ,all(DimCalendar)
    ,Fact[Column_A]>0
)

var MaxYear=
CALCULATE(
    max(DimCalendar[Year])
    ,DimCalendar[ID_Data]=MaxIDData
    ,ALL(DimCalendar)
)
return MaxYear

i get the value as i want when i use this measure in Card or table.

However when i try to use this measure as condition i am getting Placeholder true/false error:

PrevYearSales=
Calculate(
    [Sales]
    ,DimCalendar[Year] = [MaxYear] -1
)

Can anyone point out a general approach how to calcualte max Year/ID_Date to use it freely as condition in other measures?

Thank you in advance

[Anonymous · Accepted solution]

Hi @szczawek ,

Try:

PrevYearSales =
CALCULATE (
    [Sales],
    FILTER ( ALLSELECTED ( DimCalendar ), DimCalendar[Year] = [MaxYear] - 1 )
)

Which table does your [Sales] formula calculate the value in? If it is a fact table, it should use fact[year]=[maxyear]-1.

Best Regards,

Jay

[AlB]

Hi @szczawek 

Try

PrevYearSales =
VAR aux_ = [MaxYear] - 1
RETURN
    CALCULATE ( [Sales], DimCalendar[Year] = aux_ )

Please accept the solution when done and consider giving a thumbs up if posts are helpful.  Contact me privately for support with any larger-scale BI needs, tutoring, etc.