Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
I am using a couple of very simple expressions that have worked in the past. My talbe has purchase amounts for our vendors labled as Amount. I have filtered my report to 2015 then created a measure to calculate the previous year purchases and called it Previous Year Purchases.
Previous year Purchases = CALCULATE(SUM('BB$Purch_ Inv_ Line'[Amount]), SAMEPERIODLASTYEAR(DimDate1[DateKey]))
I would like to known the year over year growth so my next measure (the first one not working correctly) is subtracting previous year purchases from current year purchases. The table Name is BB$Purch_Inv_Line. Now normally, I would just use the expression =[Sum of Amount]-[Previous year] but it is not recognizing that name so I tried 'Purch_Inv_Line'[Amount] and of course it said there were to many variables so I went to = (SUM('BB$Purchase Line'[Amount]))-[Previous year Purchases] Which returns an amount that is not correct. Bellow is a snip of the amounts returned. Can anyone tell me what I have done incorrectly?
This is the expression : $Variance Current Year = (SUM('BB$Purchase Line'[Amount]))-[Previous year Purchases]
The other expression I am not having success with is :
%Variance Current Year = (SUM('BB$Purchase Line'[Amount])-[Previous year Purchases])/[Previous year Purchases]
Proud to be a Super User!
Can you share more details like what is the filter context in the table ( nothing, rows with years, products etc). Did you create a measure or a calculated field?
I think there is a bug in that particular data model. I tried to snag some data to carry over and it will not refresh. It says a key does not match a table. I have not received a response from support on this issue. PowerPivot works fine with both the calculation and the data table so i may have to shelve this question for now. As soon as the data becomes accessible I will post some more information.
More context: Rows were week numbers filtered by a manufacturer code. Totals were pulled from a single column in the table and filtered by year. Have the DAX working correctly by using DateAdd - 1 year instead of SamePeriodLastYear.
Proud to be a Super User!
Can you post a sample of the original data table?