Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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!
Solved! Go to 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
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
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
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
Hi, @feralvarez994
Can you check this example?
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 !!!
Proud to be a Super User!
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.
User | Count |
---|---|
98 | |
90 | |
82 | |
73 | |
67 |
User | Count |
---|---|
115 | |
102 | |
98 | |
71 | |
66 |