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,
I need help creating a measure that will count the total merch booked from a previous working day.
I currently have the following:
1. dimDate table
A. This table contains following:
i. Date Column
ii. Dayofweek column: 1=Sunday, 2=Monday, 3=Tuesday, 4=Wednesday, 5=Thursday, 6=Friday, 7=Saturday
iii. Working Day column: indicates whether it is a "Working" or "Non-Working" day based on the combination of "Dayofweek" and "Holiday" Column.
2. Total Merch Booked measure
Here are the conditions that this previous day measure should follow:
1. Weekday (TODAY()) = 2 (Monday), then it should look whether the Friday before was a working day, if so, then it should calculate Total Merch booked on that day, otherwise keep repeating to the previous day to it until it finds a working day and then calculate Total Merch Booked.
2. Weekday(TODAY()) =1 OR Weekday (TODAY()) =7 (Saturday or Sunday), then skip and do not calculate the Total Merch booked.
3. Weekday(TODAY()) = any other number besides 1, 2 or 7 (Tuesday thru Friday), then it should look at the previous day to see if it is a Working day, if so, then it should calculate Total Merch booked on that day, otherwise going in backwards until it finds a working day and then calculate Total Merch booked.
Solved! Go to Solution.
@minishshah, try this (adjust for your table/column names):
1. Create a calculated column in your date table:
Working Day Number Cumulative =
VAR vWorkdays =
FILTER (
ALL ( 'Date' ),
'Date'[Is Working Day] = 1
)
VAR vResult =
IF (
'Date'[Is Working Day] = 0,
BLANK (),
RANKX ( vWorkdays, 'Date'[Date],, ASC )
)
RETURN
vResult
2. Create a measure:
Previous Day Sales =
VAR vWorkingDaysOffset = 1
VAR vMaxWorkingDay =
MAX ( 'Date'[Working Day Number Cumulative] )
VAR vPreviousWorkingDay =
FILTER (
ALL ( 'Date' ),
'Date'[Working Day Number Cumulative] = vMaxWorkingDay - vWorkingDaysOffset
)
VAR vPreviousDate =
MAXX ( vPreviousWorkingDay, 'Date'[Date] )
VAR vResult =
CALCULATE ( [Total Amount], ALL ( 'Date' ), 'Date'[Date] = vPreviousDate )
RETURN
vResult
This approach assumes you have a column [Is Working Day] where 1 = true, 0 = false.
Proud to be a Super User!
@minishshah , refer to my blog -.Traveling Across Workdays -
https://community.powerbi.com/t5/Community-Blog/Travelling-Across-Workdays-Decoding-Date-and-Calenda...
Thank you for posting a link. it is interesting, but not quiet what I am looking for as stated in my initial post.
Further, I think i need to use combination of two or more columns which i indicated on my post to figure it out, but not sure.
I tried to use the following, but i also need to count 'holidays' in to the mix:
IF(
WEEKDAY(TODAY()) = 2,
CALCULATE(
[Total Merch Booked],
'dimDate'[Date]= (TODAY()-3)
),
IF(
WEEKDAY(TODAY()) = 1,
BLANK(),
IF(
WEEKDAY(TODAY()) = 7,
BLANK(),
CALCULATE(
[Total Merch Booked],
'dimDate' [Date] = (TODAY()-1)
)
)
)
I'm still needing assistance. Please help
@minishshah, try this (adjust for your table/column names):
1. Create a calculated column in your date table:
Working Day Number Cumulative =
VAR vWorkdays =
FILTER (
ALL ( 'Date' ),
'Date'[Is Working Day] = 1
)
VAR vResult =
IF (
'Date'[Is Working Day] = 0,
BLANK (),
RANKX ( vWorkdays, 'Date'[Date],, ASC )
)
RETURN
vResult
2. Create a measure:
Previous Day Sales =
VAR vWorkingDaysOffset = 1
VAR vMaxWorkingDay =
MAX ( 'Date'[Working Day Number Cumulative] )
VAR vPreviousWorkingDay =
FILTER (
ALL ( 'Date' ),
'Date'[Working Day Number Cumulative] = vMaxWorkingDay - vWorkingDaysOffset
)
VAR vPreviousDate =
MAXX ( vPreviousWorkingDay, 'Date'[Date] )
VAR vResult =
CALCULATE ( [Total Amount], ALL ( 'Date' ), 'Date'[Date] = vPreviousDate )
RETURN
vResult
This approach assumes you have a column [Is Working Day] where 1 = true, 0 = false.
Proud to be a Super User!
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 |
---|---|
106 | |
98 | |
75 | |
63 | |
62 |
User | Count |
---|---|
135 | |
105 | |
104 | |
80 | |
65 |