Trying to create TYD Variance measure with variables and not getting a result

[DebbieE]

YTD Sales Var = VAR CY = [Total Sales]

VAR TYTD = TOTALYTD([Total Sales],DimDate[FullDateAlternateKey])

VAR LYTD = CALCULATE(TYTD,SAMEPERIODLASTYEAR(DimDate[FullDateAlternateKey]))

RETURN TYTD-LYTD

this is coming up as nothing

When I test I look just at TYTD

YTD Sales Var = VAR CY = [Total Sales]

VAR TYTD = TOTALYTD([Total Sales],DimDate[FullDateAlternateKey])

VAR LYTD = CALCULATE(TYTD,SAMEPERIODLASTYEAR(DimDate[FullDateAlternateKey]))

RETURN TYTD

And this brings up the year to date values no problem

And the next test I replace the last part with RETURN LYTD

And again, you get the Last year to date totals showm so both  TYTD and LYTD return the correct values

So its just this RETURN TYTD-LYTD

this returns 0 and I cant figure out why at the moment. Im using Adventureworks data warehouse data to test on

Any help would be appreciated

[v-xicai]

Hi @DebbieE ,

In DAX, variables are calculated within the scope in which they are written, and then the result of them is stored and used in the rest of the expression. See more: Caution When Using Variables in DAX and Power BI.

You may change the formula like DAX below.

YTD Sales Var =

VAR TYTD = TOTALYTD(SUM(Table1[Sales]), DimDate[FullDateAlternateKey])

VAR LYTD = CALCULATE(SUM(Table1[Sales]) ,SAMEPERIODLASTYEAR(DimDate[FullDateAlternateKey]))

RETURN TYTD-LYTD

For reference:

https://community.powerbi.com/t5/Desktop/var-different-result/td-p/283143

https://community.powerbi.com/t5/Desktop/VAR-different-result-than-without-it/td-p/518723

Best Regards,

Amy 

Community Support Team _ Amy

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[DebbieE]

VAR LYTD = CALCULATE (SUM (Table1 [Sales]), SAMEPERIODLASTYEAR (DimDate [FullDateAlternateKey]))

But this isnt calculating the Year to date measure that Im basing last year on so this wouldnt work?

As in my TYTD variable

TOTALYTD (SELECTEDMEASURE (), DimDate [FullDateAlternateKey])

I should say, Im trying to do some work on calulated groups with Tabular Editor and Im trying to find ways of recreating my measures so they could be used for calculated groups and its proving really difficult

[DebbieE · Accepted solution]

Ive got it

YTD Sales Var = VAR CY = [Total Sales]

VAR LYTD = CALCULATE(TOTALYTD([Total Sales],DimDate[FullDateAlternateKey]),SAMEPERIODLASTYEAR(DimDate[FullDateAlternateKey]))

RETURN TYTD-LYTD

I have to add the TYTD into LYTD to make that work because I cant use a variable within another variable like you said

[amitchandak]

@DebbieE , this should work. DimDate is marked as date table (Option on right click)

FullDateAlternateKey is a date, not the numeric key

[Greg_Deckler]

@DebbieE - You may find this helpful - https://community.powerbi.com/t5/Community-Blog/To-bleep-With-Time-Intelligence/ba-p/1260000

Also, see if my Time Intelligence the Hard Way provides a different way of accomplishing what you are going for.

https://community.powerbi.com/t5/Quick-Measures-Gallery/Time-Intelligence-quot-The-Hard-Way-quot-TIT...