Create a Variable for Text Box's

[Anonymous]

Every month we run a report every month.  We navigate through 7 or 8 sheets and manually change the text box's to have the updated month(Jan, Feb, Etc.).  

Is there a way to create an object in the text box's so we only have to change one text box and it automatically updates all the other pages?

Thanks!

[Anonymous · Accepted solution]

Hi  @Anonymous ,

I created some data:

In Excel,

Sheet1:

2022.1.1 – 2022.2.28

Sheet2:

2022.1.1 – 2022.2.23

Sheet3:

2022.1.1 – 2022.2.16

Here are the steps you can follow：

1. Create measure.

Flag1 =
IF(
YEAR( MAX('Sheet1'[Date]))=YEAR(MAXX(ALL('Sheet1'[Date]),[Date]))&&MONTH(MAX('Sheet1'[Date]))=MONTH(MAXX(ALL('Sheet1'[Date]),[Date])),1,0
)

Flag2 =
IF(
YEAR( MAX('Sheet2'[Date]))=YEAR(MAXX(ALL('Sheet2'[Date]),[Date]))&&MONTH(MAX('Sheet2'[Date]))=MONTH(MAXX(ALL('Sheet2'[Date]),[Date])),1,0
)

Flag3 =
IF(
YEAR( MAX('Sheet3'[Date]))=YEAR(MAXX(ALL('Sheet3'[Date]),[Date]))&&MONTH(MAX('Sheet3'[Date]))=MONTH(MAXX(ALL('Sheet3'[Date]),[Date])),1,0
)

2. Put [Flag1] , [Flag2] , [Flag3] in the corresponding Visual - Filter in Page1, Page2, Page3.

3. When you update to a new date in Excel, you can directly click Refresh, and then Page1, Page2, and Page3 will display the data of the current month on the latest date

4. Result:

Update the date to March in Excel, after refreshing, the data for March is displayed in Page1.

Best Regards,

Liu Yang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly

[truptis]

Hi @Anonymous ,
If you want to show the latest month when the refresh occured, you can create a measure - 

M1 = var latest_date = MAX(date)
return MONTH(latest_date)

So, if you have refreshed the report today(14th March 2022), latest_date will be 14th March 2022 and M1 will return MARCH. 

You can have a card visual and put this measure in it. You can copy paste the same card on all the pages where you want to show the month. 

By this way, everytime the report gets refreshed, the text on the card would change dynamically. 

@Anonymous if this helps you, then please mark it as a solution and hit the thumbs up. Thank you.

Regards,

TruptiS