Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hi experts,
I need to develop below matrix with few conditions
The sales amount will be depostited on the same day in case of bank working days.
Otherwise they will deposit on next banking day.
In below example, as you can see banks are closed on 25th and 26th december (Christmas and Sunday)
So they done the deposit on 27th december (25,26 and 26th sales).
Please help.
25-12-2021 | 26-12-2021 | 27-12-2021 | 28-12-2021 | 29-12-2021 | |||
Sale | Sale | Sale | Deposit | Sale | Deposit | Sale | Deposit |
₹1,000.00 | ₹500.00 | ₹2,000.00 | ₹3,500.00 | ₹100.00 | ₹100.00 | ₹50.00 | ₹50.00 |
Solved! Go to Solution.
Hi,
I am not sure about which dates are your holidays, but I tried to create the sample like below.
Please check the below picture and the attached pbix file.
All measures are in the attached pbix file with numbering the steps from 01 to 05.
In the sample, I assigned Saturday and Sunday as holidays, but you can assign your own holidays in your calendar table, and then apply the similar logic as what are in the attached pbix file.
I hope this helps to apply and write similar measures for your data model.
If this post helps, then please consider accepting it as the solution to help other members find it faster, and give a big thumbs up.
Hi,
I am not sure about which dates are your holidays, but I tried to create the sample like below.
Please check the below picture and the attached pbix file.
All measures are in the attached pbix file with numbering the steps from 01 to 05.
In the sample, I assigned Saturday and Sunday as holidays, but you can assign your own holidays in your calendar table, and then apply the similar logic as what are in the attached pbix file.
I hope this helps to apply and write similar measures for your data model.
If this post helps, then please consider accepting it as the solution to help other members find it faster, and give a big thumbs up.
Thanks @Jihwan_Kim , this is the exact solution I want. Thanks for your effort and time.
@Anonymous I'm hoping you have Date dimension table in the Model. if not please do.
Step 1
Create one Calculated column in the Date table
IsWorkingDay =
VAR Result =
IF(
dcalendar[WeekDay] IN {1,7} || dcalendar[Holiday],
0,
1
)
RETURN
Result
Step 2:- Create "ThisorNextWorkingDay" calculated column
ThisorNextWorkingDay =
VAR NextWorkingDay =
MINX(
FILTER(
dcalendar,
dcalendar[Date] > EARLIER( dcalendar[Date])
&& dcalendar[IsWorkingDay] = 1
),
dcalendar[Date]
)
VAR Result =
IF(
dcalendar[IsWorkingDay] = 0 ,NextWorkingDay , dcalendar[Date]
)
RETURN
Result
Output:-
Please mark the question solved when done and consider giving kudos if posts are helpful.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
114 | |
100 | |
81 | |
70 | |
62 |
User | Count |
---|---|
148 | |
116 | |
104 | |
90 | |
65 |