Referencing one DAX measure in another

[navpienaar]

I know there are other ways of doing this, but I want to know if there is a way to do the following:

I want to reference Measure 1 in Measure 2

Measure 1 is OK and working

Measure 1:         vMax_Year_PY  = Max('SALES'[Year])-1

                Note:     Year is a separate field in the Sales table

                              I want to treat vMax_Year_PY  almost as a “global variable” in different measure functions…

Measure 2:        CALCULATE(SUM('Sales'[Sales]),'Sales'[Year] = vMax_Year_PY )

Is there any syntax to make this type of function work??

[Zubair_Muhammad]

@navpienaar

Try this one

Measure 2 =
VAR MyYear = [vMax_Year_PY]
RETURN
    CALCULATE (
        SUM ( 'Sales'[Sales] ),
        FILTER ( ALLSELECTED ( Sales[YEAR] ), 'Sales'[Year] = MyYear )
    )

[navpienaar]

Hi Zubair

I am getting part of the correct answer with the following if I select a specific Year:

Sales PY =
VAR MyYear = [vMax_Year_PY]
RETURN CALCULATE ( SUM ( TRANS[Sales] ), FILTER ( ALL ( TRANS[YEAR] ), TRANS[YEAR] = MyYear ) )

However, it does not give me all the Sales for the Previous Year (all products), if in the there were no sales for specific products in the selected year.

Please see the image and Sandbox App in the link: https://drive.google.com/drive/folders/1ZPtPMgDm1CID4AnezDGsu49ve1vY0EtP?usp=sharing

https://drive.google.com/drive/folders/1ZPtPMgDm1CID4AnezDGsu49ve1vY0EtP?usp=sharing