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.
I've created a new column DAYS_OPEN to get datediff between REPORTED_DATE and RESOLVED_DATE.
But, sometimes we won't have RESOLVED_DATE due to STATUS of the item is "OPEN".
For my project purposes I need to show by line each week number, so I'm finding a function that I get the last day of each week (ex: week 12 (16 to 22/mar) I need to get the last 22/mar/20 as RESOLVED_DATE).
Someone can help me? Sorry if it seems basic question, I'm new using PBI.
See below my DAX for DAYS_OPEN column:
Solved! Go to Solution.
I don't understand this
but it is necessary to apply an unique rule for each week (13, 14, and so on).
if we can get a session going today perhaps it will be clearer to me.
Help when you know. Ask when you don't!
Hi @kentyler
I'm thankful for you. Our call had super helpful for me to solve this problem.
I'm posting below the solution we've discussed during the call.
You really saved a lot of my time on this project.
Sorry for the delay to reply the thanks for you and the community.
Hi,
This calculated column formula works
=IF(ISBLANK(Data[Resolved date]),TODAY()-SWITCH(WEEKDAY(today(),2),1,1,2,2,3,3,4,4,5,5,6,6,7,0),Data[Resolved date])
Hope this helps.
I applied your suggestion.
But I need something like this:
For week 12 (letter A above): for those items without resolved_date (status 'open') I need to consider the end date of the week (22/03/20) as 'resolved_date' but it is necessary to apply an unique rule for each week (13, 14, and so on).
My last DAX code:
I don't understand this
but it is necessary to apply an unique rule for each week (13, 14, and so on).
if we can get a session going today perhaps it will be clearer to me.
Help when you know. Ask when you don't!
Hi @kentyler
I'm thankful for you. Our call had super helpful for me to solve this problem.
I'm posting below the solution we've discussed during the call.
You really saved a lot of my time on this project.
Sorry for the delay to reply the thanks for you and the community.
DAYS_OPEN =
IF (
'MEL_ITEMS_TEMPO_MÉDIO_WEEKLY'[STATUS] = "CLOSED";
(
DATEDIFF (
'MEL_ITEMS_TEMPO_MÉDIO_WEEKLY'[REPORTED_DATE];
'MEL_ITEMS_TEMPO_MÉDIO_WEEKLY'[RESOLVED_DATE];
DAY
)
);
(
DATEDIFF (
VAR resolved_date = IF(ISBLANK( 'MEL_ITEMS_TEMPO_MÉDIO_WEEKLY'[RESOLVED_DATE]),
Data[Date] – WEEKDAY(Data[Date],2) + 7,
'MEL_ITEMS_TEMPO_MÉDIO_WEEKLY'[RESOLVED_DATE])
'MEL_ITEMS_TEMPO_MÉDIO_WEEKLY'[REPORTED_DATE];
resolved_date;
DAY
)
)
)
I practice Slow BI
Help when you know. Ask when you don't!
You could try using reported_date that would mean, if there is no resolved date, assumed the resolved date is the end of the week in which it was reported.
Help when you know. Ask when you don't!
I've implemented this suggestion but the error is persisting.
I put return... it's necessary? Even it is not running well.
Can you post a sample powerbi file ? or do a screen share ?
I can possible fix it in the morning.
Help when you know. Ask when you don't!
Sounds good. I can share my screen.
Do you have start zoom account?
What your time zone?
My time zone
looks like you are 4 hrs ahead of me
do you want to do something in the morning
8 your time 12 my time ?
Help when you know. Ask when you don't!
you got it, send me a meeting invitation
i'm ken at 8thfold dot com
Help when you know. Ask when you don't!
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 |
---|---|
104 | |
101 | |
79 | |
72 | |
64 |
User | Count |
---|---|
143 | |
109 | |
103 | |
82 | |
74 |