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 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?
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 |
---|---|
96 | |
95 | |
80 | |
74 | |
66 |
User | Count |
---|---|
130 | |
106 | |
105 | |
86 | |
72 |