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.
Hello.
The basis of my problem is this:
Beginning | End | Cycle |
Fri 11:00:00 | Mon 10:59:59 | 30 |
Mon 11:00:00 | Tue 10:59:59 | 40 |
Tue 11:00:00 | Wen 10:59:59 | 50 |
Wed 11:00:00 | Thu 10:59:59 | 60 |
Thu 11:00:00 | Mon 10:59:59 | 20
|
Each row represent an order, which I have the information of: Payment Date, Number of Articles, Billing Date and So on. I already converted the payment date to a cycle, by following the structure in the table ahead.
Now, I have these columns: [Cycle] (Text), [PaymentWeekDay] (Text), [PaymentDay (only day in date format)]. I need the limit date where each order correspond to a cycle. Example: If an order was payed today (31/12/2020) my limit date is 04/01/2021 - cycle 20.
I'm following this formula but I still have blanks:
=SWITCH(TRUE();Table[Cycle]="Cycle 40"&&Table[PaymentWeekDay]="monday";DATEADD(Table[PaymentDay];+2;DAY);[Cycle]="Cycle 40"&&Table[PaymentWeekDay]="tuesday";DATEADD(Table[PaymentDay];+1;DAY);[Cycle]="Cycle 50"&&Table[PaymentWeekDay]="tuesday";DATEADD(Table[PaymentDay];+2;DAY);Table[Cycle]="Cycle 50"&&Table[PaymentWeekDay]="wednesday";DATEADD(Table[PaymentDay];+1;DAY);Table[Cycle]="Cycle 60"&&Table[PaymentWeekDay]="wednesday";DATEADD(Table[PaymentDay];+2;DAY);Table[Cycle]="Cycle 60"&&Table[PaymentWeekDay]="thrusday";DATEADD(Table[PaymentDay];+1;DAY);Table[Cycle]="Cycle 20"&&Table[PaymentWeekDay]="thrusday";DATEADD(Table[PaymentDay];+4;DAY);Table[Cycle]="Cycle 20"&&Table[PaymentWeekDay]="friday";DATEADD(Table[PaymentDay];+3;DAY);Table[Cycle]="Cycle 30"&&Table[PaymentWeekDay]="friday";DATEADD(Table[PaymentDay];+4;DAY);Table[Cycle]="Cycle 30"&&Table[PaymentWeekDay]="saturday";DATEADD(Table[PaymentDay];+3;DAY);Table[Cycle]="Cycle 30"&&Table[PaymentWeekDay]="sunday";DATEADD(Table[PaymentDay];+2;DAY);Table[Cycle]="Cycle 30"&&Table[PaymentWeekDay]="monday";DATEADD(Table[PaymentDay];+1;DAY);0)
Any Ideas?
Have a look at the TREATAS() function. It might help you to apply your filters more economically.
I assume "thrusday" is just a typo?
Generally you will want to have a calendar/dates table in your data model so you can use Time Intelligence DAX functions rather than having to code this all manually.
Maybe you can supply sample data so we can assist better.
I meant thursday (I changed all of weekday entries to english) , I used a dax conversion of a date to weekday. I can't supply data, sorry...
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 |
---|---|
49 | |
27 | |
20 | |
15 | |
12 |
User | Count |
---|---|
58 | |
50 | |
44 | |
19 | |
18 |