Forum Discussion
Relative reference with measure
Is there a way to create a relative reference within a measure? In my model I currently need to update 6 different measures when we reach a certain point throughout the month. This point in the month does not equal a specific date point so I am not able to use time intelligence. Also the point in the month is referred to by a fiscal month not calendar month. Is there a way to write the measure to reference a specific table similar to a relative reference in Excel? Then I could create another table in the model. The only way I have been able to think about it so far is to have a manual list of every date in the year and the future year and give it the month numberical reference but that is a lot of maintenance year over year.
Thanks!
7 Replies
Anonymous not sure what you are trying to achieve. can you share sample data and expected output. Read this post to get your answer quickly.
https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490Anonymous , not very clear. But if you have a custom period and you want to do time intelligence
Power BI Working with Non-Standard Time
https://community.powerbi.com/t5/Community-Blog/Week-Is-Not-So-Weak-WTD-Last-WTD-and-This-Week-vs-Last-Week/ba-p/1051123hi Anonymous
Sample data and expected output would help tremendously.
Please see this post regarding How to Get Your Question Answered Quickly:
https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490Regards,
Lin
Hello,
There have been many requests for my actual data set which I am not sure is necessary. This is a process driven question. Within Excel there is the option to use the cell reference which can alter a calculation. I am wondering if there is any way to do this in Power BI.
https://edu.gcfglobal.org/en/excelformulas/relative-and-absolute-cell-references/1/
hi Anonymous
Yes, you could get it, you could use ALLxxx function to clear the row context and add the fixed conditions in the formula.
If you still have the problem, please share the sample pbix file and your expected output.
Regards,
Lin
Thanks v-lili6-msft
Unfortunately I may not be fully understanding. I tried to follow the link you sent and was unsuccessful in getting the measure to function as expected.
My original formula is below and I have to manually update the Fiscal Period every month.
Selected HC VTF = if(HASONEFILTER('Z-Fiscal_Time'[Fiscal Period]),calculate(sum(EID_Table[Forecast People]),EID_Table[Staffing Type Category]="Regular"),calculate(sum(EID_Table[Forecast People]),EID_Table[Staffing Type Category]="Regular",'Z-Fiscal_Time'[Fiscal Period]="P11 - FY20")) -if(HASONEFILTER('Z-Fiscal_Time'[Fiscal Period]),calculate(sum(EID_Table[Actual People]),EID_Table[Staffing Type Category]="Regular"),calculate(sum(EID_Table[Actual People]),EID_Table[Staffing Type Category]="Regular",'Z-Fiscal_Time'[Fiscal Period]="P11 - FY20"))I have created a related table that I would like to be able to slice on instead of having to update the measure. With those details are you able to let me know of a way to replace the Fiscal Period?Thanks!
