Good Evening All
I was hoping for some advice. Yes another one 🙂
Please see images below of 2x Columns - Fig 1, Fig 2
Fig 1: Context
Its a whole number column which is produced frrom a DateDiff calculation. Today()- Date the task was completed. This is 100% working fine and no issues here.
So if there are blanks - the task is not completed
If the value =0 The task was completed today
If the value = -5 The task was completed in the last 5x days
If the value = -10 The task was completed in the last 10x days
etc
Fig 2: Context
A DAX calculated column based upon the Fig 1 column numbers. - Always 0 or less
Its working partially.
If the task is blank I get the right message.
If the value =0 (completed today) I get the right message.
However all other outputs are as you can see are all - Task was completed in the last 10x days.
DAX Calculated Column
GROUP_COMPLETED DATES = Switch(true() ,
isblank(tbl_POAP_master[Days since Completion]) , "No Yet Complete",
tbl_POAP_master[Days since Completion] = 0, "Completed Today",
tbl_POAP_master[Days since Completion] < 0, "Complete in last 10x Days",
tbl_POAP_master[Days since Completion] <= -10, "Complete in last 10x Days",
tbl_POAP_master[Days since Completion] <= -20, "Complete in last 20x Days",
"Completed 20+ Dasy Ago")
I have pasted the results below and Im sure its obvious to some on how Ive formatted my if statements and why all items that are completed seem to be all marked as completed in the last 10x days (apart from the one I show as completed today- which works)
Fig 1
Fig 2
Appreciate any advice
Jimmy
Hi @jimmyg706 ,
If you use <=-20 in switch, all data <=-20 will return Complete in last 20x Days instead of Completed 20+ Days Ago
I suggest you to try code as below to create a calculated column.
GROUP_COMPLETED DATES =
SWITCH (
TRUE (),
ISBLANK ( tbl_POAP_master[Days since Completion] ), "No Yet Complete",
tbl_POAP_master[Days since Completion] = 0, "Completed Today",
tbl_POAP_master[Days since Completion] > -20, "Complete in last 10x Days",
tbl_POAP_master[Days since Completion] > -30, "Complete in last 20x Days",
"Completed 20+ Days Ago"
)Result is as below.
Best Regards,
Rico Zhou
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
let me ask you couple of questions:
- Is this a column or a measure?
[Days since Completion]
- Are you adding this as a column or a measure?
GROUP_COMPLETED DATESSubscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!
Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo
If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤
Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.
@jimmyg706 you need to change the order:
GROUP_COMPLETED DATES = Switch(true() ,
isblank(tbl_POAP_master[Days since Completion]) , "No Yet Complete",
tbl_POAP_master[Days since Completion] = 0, "Completed Today",
tbl_POAP_master[Days since Completion] <= -20, "Complete in last 20x Days",
tbl_POAP_master[Days since Completion] <= -10, "Complete in last 10x Days",
tbl_POAP_master[Days since Completion] < 0, "Complete in last 10x Days",
"Completed 20+ Dasy Ago") Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!
Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo
If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤
Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.
Hi @jimmyg706 ,
If you use <=-20 in switch, all data <=-20 will return Complete in last 20x Days instead of Completed 20+ Days Ago
I suggest you to try code as below to create a calculated column.
GROUP_COMPLETED DATES =
SWITCH (
TRUE (),
ISBLANK ( tbl_POAP_master[Days since Completion] ), "No Yet Complete",
tbl_POAP_master[Days since Completion] = 0, "Completed Today",
tbl_POAP_master[Days since Completion] > -20, "Complete in last 10x Days",
tbl_POAP_master[Days since Completion] > -30, "Complete in last 20x Days",
"Completed 20+ Days Ago"
)Result is as below.
Best Regards,
Rico Zhou
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Helpful resources
Power BI Monthly Update - April 2024
Check out the April 2024 Power BI update to learn about new features.
Fabric Community Update - April 2024
Find out what's new and trending in the Fabric Community.
