Relative reference with measure

[alicia_pbi]

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!

[alicia_pbi]

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://support.office.com/en-us/article/Switch-between-relative-absolute-and-mixed-references-DFEC0... 

https://edu.gcfglobal.org/en/excelformulas/relative-and-absolute-cell-references/1/

[v-lili6-msft]

hi  @alicia_pbi 

Yes, you could get it, you could use ALLxxx function to clear the row context and add the fixed conditions in the formula.

https://www.sqlbi.com/articles/managing-all-functions-in-dax-all-allselected-allnoblankrow-allexcept...

If you still have the problem, please share the sample pbix file and your expected output.

Regards,

Lin

[alicia_pbi]

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!

[v-lili6-msft]

hi  @alicia_pbi 

Please share your sample pbix file and  your expected output, that will be a great help.

Regards,

Lin

[v-lili6-msft]

hi @alicia_pbi 

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/38490

Regards,

Lin

[amitchandak]

@alicia_pbi , 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-La...

[parry2k]

@alicia_pbi 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/38490