Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and a 50 percent discount on exams.
Get startedEarn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
Hi,
Need suggestions on how to resolve the logic issue I have right now.
Expected Outcome:
Data: | ||||
ID | Date Opened | Date Closed | ||
1 | Jul-17 | |||
2 | Jul-17 | Aug-17 | ||
3 | Aug-17 | Aug-17 | ||
4 | Sep-17 | |||
5 | Oct-17 | Oct-17 | ||
Jul-17 | Aug-17 | Sep-17 | Oct-17 | |
NEW | 2 | 1 | 1 | 1 |
OPEN | 2 | 1 | 2 | 2 |
NFA | 1 | 1 |
Logic:
Data is counted as New if it is opened within the month
Data is counted as Open if it is not closed within the month (data opened on a different month is counted as well)
Data is counted as NFA if it is closed within the month
Current Power BI Behaviour:
Jul-17 | Aug-17 | Sep-17 | Oct-17 | |
NEW | 2 | 1 | 1 | 1 |
OPEN | 1 | 1 | 2 | 2 |
NFA | 1 | 1 |
Analysis: Power BI doesn't consider/count data with Closed Date as Open.
Data:
I have Data Table (Table1) and a Date Table (New Table created in Power BI).
Logic in counting Open Data -
New Column:
IsOpen = IF(ISBLANK('Table1'[closeddate]) &&
'Table1'[OpenedClosed_SameMonth] <> 1, 1,0)
New Quick Measure (Running Total):
OPEN =
CALCULATE(
SUM('Table1'[IsOpen]),
FILTER(
CALCULATETABLE(
SUMMARIZE('Date', 'Date'[MonthYearInt], 'Date'[MonthYear]),
ALLSELECTED('Date')
),
ISONORAFTER(
'Date'[MonthYearInt], MAX('Date'[MonthYearInt]), DESC,
'Date'[MonthYear], MAX('Date'[MonthYear]), DESC
)
)
)
Thank you in advance!
Solved! Go to Solution.
Hi,
Here's the result i got. You may download my PBI file from here. Hope this helps.
Maybe using OR instead of AND in the condtion would work:
old:
IsOpen = IF(ISBLANK('Table1'[closeddate]) &&
'Table1'[OpenedClosed_SameMonth] <> 1, 1,0)
new:
IsOpen = IF(ISBLANK('Table1'[closeddate]) ||
'Table1'[OpenedClosed_SameMonth] <> 1, 1,0)
Imke Feldmann (The BIccountant)
If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!
How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries
Hi, I tried "||" but it gives me this result.
Jul-17 | Aug-17 | Sep-17 | Oct-17 | |
NEW | 2 | 1 | 1 | 1 |
OPEN | 2 | 3 | 5 | 5 |
NFA | 1 | 1 |
the succeeding month mismatch with the expected output.
@seira88,
What field of table1 do you use to create relationship between table1 and date table? Do you get expected result when you directly drag isOpen column to your visual?
Also please share us your Date table.
Regards,
Lydia
Hi Lydia,
I created DateAsInt field in both Table1 and Date tables and that is what I used to link them.
As per IsOpen, I cannot directly use it as I won't see the value of previous months wherein thereis no data created. (see example below)
Data: | ||
ID | Date Opened | Date Closed |
1 | Jul-17 | |
2 | Jul-17 | Aug-17 |
3 | Aug-17 | Aug-17 |
4 | Oct-17 | |
5 | Nov-17 | Nov-17 |
Expected Outcome:
Jul-17 | Aug-17 | Sep-17 | Oct-17 | Nov-17 | |
NEW | 2 | 1 | 0 | 1 | 1 |
OPEN | 2 | 1 | 1 | 2 | 2 |
NFA | 1 | 0 | 1 |
Power BI Result:
Jul-17 | Aug-17 | Sep-17 | Oct-17 | Nov-17 | |
NEW | 2 | 1 | 1 | 1 | |
OPEN | 1 | 1 | 0 | 2 | 2 |
NFA | 1 | 1 |
Hope this answered your clarifications. Thanks!
Hi,
Here's the result i got. You may download my PBI file from here. Hope this helps.