variation without date dimension

[ftorres]

Hi everyone,

I'm trying to create a measure that brings me the variation.

Like this table below. But I would like instead of accumulating I would like the variation between them. I know with the date functions but the account period is not a date type.

% Previous Month = CALCULATE([Actual Cost],FILTER(ALLSELECTED(Cost),Cost[account_period]<= MAX(Cost[account_period])))

[ssugar · Accepted solution]

If your accounting period is a text field, you can do this:

Previous Month = CALCULATE([Actual Cost],FILTER(ALLSELECTED(Table1),IF(RIGHT(SELECTEDVALUE(Table1[Accounting Period]), 2) = "01", VALUE(SELECTEDVALUE(Table1[Accounting Period])) = VALUE(Table1[Accounting Period]) + 89,VALUE(SELECTEDVALUE(Table1[Accounting Period])) = VALUE(Table1[Accounting Period]) + 1)))

If it's a numeric field, you can do this:

Previous Month = CALCULATE([Actual Cost],FILTER(ALLSELECTED(Table1),IF(RIGHT(FORMAT(SELECTEDVALUE(Table1[Accounting Period]), "General Number"), 2) = "01", SELECTEDVALUE(Table1[Accounting Period]) = Table1[Accounting Period] + 89,SELECTEDVALUE(Table1[Accounting Period]) = Table1[Accounting Period] + 1)))

Then to get the %:

% Previous Month = IF([Previous Month] = BLANK(), "NA", [Actual Cost] / [Previous Month])

Note that this will only work if you have no gaps in your Accounting Periods.  In your example data, you have a gap between 201111 and 201202, so this will break down there. 

link to a pbix file with working solution for first 4 rows of your example data:

https://github.com/ssugar/PowerBICommunity/raw/master/community-sol-265464.pbix