Calculate the USD price the fourth working day of the month

[feralvarez994]

Hi everyone! I have three tables:

- One table with monthly salaries

- Table with USD - Argentinian pesos equivalency per day (Venta column is the price that i have to use, and it means how many pesos are iqual to 1 usd that day, example 1/1/2021 --> 1 usd = 77,14 pesos argentinos.

- Calendar table with working and non working days

-      working day = "SE LABURA" in Días laborales column.

-      non working day = "NO LABORAL"

The thing is that i want to calculate the usd price in argentinas pesos the fourth working day of each month, example in July 2018, the fourth working day is the day 5 (5th of July 2021).

Can anyone help me with this? Thank you!

[v-jayw-msft · Accepted solution]

Hi @feralvarez994 ,

Use LOOKUPVALUE() function to create a calculated column in Table with USD to get the "is working day" mark from Calendar table.

is working day = LOOKUPVALUE('calendar'[Días laborales],'calendar'[Date],'Table with USD'[date])

Then use RANKX() function to get the order of working day of each month.

_rank = RANKX(FILTER('Table with USD','Table with USD'[date].[Month]=EARLIER('Table with USD'[date].[Month])&&'Table with USD'[is working day]="SE LABURA"),'Table with USD'[date],,ASC)

The date with a monthly ranking of 4 is the fourth working day.

Best Regards,

Jay 

[feralvarez994]

Hey! It works but the thing is that it ranks well everything in the first year, the next year starts adding the data to the numbers of the previous year (it does not start from 1 again). How can i filter that? I imagine that adding a FILTER with year instead of month but i can't find the solution

Thanks a lot to everyone! Its very useful to have a community like this

[v-jayw-msft]

Hi @feralvarez994 ,

Please refer the year filter as below.

_rank = RANKX(FILTER('Table with USD','Table with USD'[date].[Month]=EARLIER('Table with USD'[date].[Month])&&'Table with USD'[date].[YEAR]=EARLIER('Table with USD'[date].[YEAR])&&'Table with USD'[is working day]="SE LABURA"),'Table with USD'[date],,ASC)

Best Regards,

Jay

[ALLUREAN]

Hi, @feralvarez994 

Can you check this example?

https://www.dropbox.com/s/27ei83fgay2tge1/USD%20price%20at%204th%20WorkingDay_%28alllure-analytics.c...

Note that in this example I use weekend as non-working day, official holidays are not taking into accout, but you can use your own calculation of working/non-working days to achieve the result

Did I answer your question? Please Like and Mark my post as a solution if it solves your issue. Thanks.

Appreciate your Kudos !!!

https://allure-analytics.com/

[lbendlin]

Please provide sanitized sample data that fully covers your issue. Paste the data into a table in your post or use one of the file services.