Forum Discussion
variation without date dimension
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])))
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
1 Reply
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