Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
Hi everyone! Im still new in power bi. Currently working on this project where i need to show the date for yesterday. For example in the image, if my date is 22/5/2020 (friday), i need to get the date for yesterday. But if my previous date have an off date, i need to get the value from all the off dates & from the day before it. Example if my date is 1/6/2020 (Monday), i need to get dates that takes from 29/5 until 31/5. The logic is it will automatically count the OFF, and also take the previous date from the last OFF date.
I have 2 columns, DATE & DATE_OFF. Kindly need advice & ideas from the experts.
Solved! Go to Solution.
Try this
Column =
VAR _PreviousDay =
MAXX (
FILTER (
MyTable,
MyTable[Date]
< EARLIER ( MyTable[Date] )
&& MyTable[Status] <> "OFF"
),
MyTable[Date]
)
VAR _result =
SUMX (
FILTER (
MyTable,
MyTable[Date]
< EARLIER ( MyTable[Date] )
&& MyTable[Date] >= _PreviousDay
),
MyTable[Cost]
)
RETURN
_result
Did I answer your question? Mark my post as a solution!
Appreciate with a kudos 🙂
@New_be , Two new columns like that.
Work Date = if(WEEKDAY([Date],2)>=6,BLANK(),[Date]) //OR
Work Date = if([Status]="OFF",BLANK(),[Date])
Last working date = maxx(filter('Date','Date'[Work Date]<EARLIER('Date'[Work Date])),'Date'[Work Date])
Date | Day | Status |
19-05-2020 | Tuesday | |
20-05-2020 | Wednesday | |
21-05-2020 | Thursday | |
22-05-2020 | Friday | |
23-05-2020 | Saturday | OFF |
24-05-2020 | Sunday | OFF |
25-05-2020 | Monday | OFF |
26-05-2020 | Tuesday | OFF |
27-05-2020 | Wednesday | |
28-05-2020 | Thursday | |
29-05-2020 | Friday | |
30-05-2020 | Saturday | OFF |
31-05-2020 | Sunday | OFF |
01-06-2020 | Monday | |
02-06-2020 | Tuesday | |
03-06-2020 | Wednesday | |
04-06-2020 | Thursday |
Create a calculated column
Previous Working Day = MAXX(FILTER(MyTable,MyTable[Date]<EARLIER(MyTable[Date])&&MyTable[Status]<>"OFF"),MyTable[Date])
Did I answer your question? Mark my post as a solution!
Appreciate with a kudos 🙂
A vey good solution! But what i mean is if the date is 1/6/2020 (monday), and we have 2 days off, we will take the data that add from date 29-31 May 2020. How to handle with it ?
@nandukrishnavs i do check your solution and it is awesome! But i think i wrongly asked before. Pardon for that. My question is how can i get previous data like in this image that i draw. For example, if the date 27/5/2020 (wednesday) and we have 4 days off from 23-26 May 2020, then i want to take the data from before the last date off that is from 22-26 May 2020. And then the value for 27/5/2020 (wed) will b e the sum of cost from22-26 May 2020.
I dont know whether it is possible to do that in power bi, but i hope with your help, i can learn something new 🤗
Try this
Column =
VAR _PreviousDay =
MAXX (
FILTER (
MyTable,
MyTable[Date]
< EARLIER ( MyTable[Date] )
&& MyTable[Status] <> "OFF"
),
MyTable[Date]
)
VAR _result =
SUMX (
FILTER (
MyTable,
MyTable[Date]
< EARLIER ( MyTable[Date] )
&& MyTable[Date] >= _PreviousDay
),
MyTable[Cost]
)
RETURN
_result
Did I answer your question? Mark my post as a solution!
Appreciate with a kudos 🙂
can i change my(cost) column in your code into a measure? i mean, can i use measure instead of column?
User | Count |
---|---|
98 | |
90 | |
82 | |
73 | |
67 |
User | Count |
---|---|
115 | |
102 | |
98 | |
71 | |
67 |