Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
I have this demotable. ColumnB has many different values, as well as Column C. Disregard ColumnA
ColumnA | Date | ColumnB | ColumnC | Calculate count |
07 | 21.9.2017 | 1000 | A | 1 |
07 | 22.9.2017 | 1000 | A | |
07 | 16.11.2017 | 1000 | A | 2 |
07 | 17.11.2017 | 1000 | A | |
07 | 8.1.2018 | 1000 | A | 3 |
07 | 9.1.2018 | 1000 | A | |
07 | 12.1.2018 | 1000 | A | 4 |
07 | 1.6.2018 | 1000 | A | 5 |
07 | 3.10.2018 | 1000 | A | 6 |
07 | 4.10.2018 | 1000 | A | |
07 | 3.1.2019 | 1000 | A | 7 |
07 | 11.2.2019 | 1000 | A | 8 |
07 | 12.2.2019 | 1000 | A |
My desired output is the Calculate count. I do not want to count the rows where the criteria meets if it has been counted for the previous day.
My effort so far is this
Calculate count = CALCULATE ( COUNTROWS(DemoTable); FILTER ( DemoTable; DemoTable[ColumnB] = EARLIER ( DemoTable[ColumnB] ) && DemoTable[Date] <= EARLIER ( DemoTable[Date] ) && DemoTable[ColumnC] = 1000 ) )
This returns 1 - 13 events so it is not filtering out the sequental days.
Any help here would be greatly appreciated.
Solved! Go to Solution.
Hi @Yggdrasill
you can do thi sin 2 steps:
first add this column:
AddedCol1 = ISEMPTY( CALCULATETABLE( Data, ALLEXCEPT( Data, Data[ColumnB], Data[ColumnC] ), Data[Date] = EARLIER( Data[Date] ) - 1 ) ) + 0
and then this final column:
AddedCol2 = IF( Data[AddedCol1] = 0, BLANK(), CALCULATE( SUM( Data[AddedCol1] ), ALLEXCEPT( Data, Data[ColumnC], Data[ColumnB] ), Data[Date] <= EARLIER( Data[Date] ) ) )
Did I answer your question correctly? Mark my answer as a solution!
Proud to be a Datanaut!
Going back to this problem.
I have defined a holiday table which is merged to my date table. I want to exclude holidays, saturdays and sundays from my counter but doesn't the function EARLIER() expect consecutive dates ?
Example of the output (Correct Calc count)
ColumnA | Date | ColumnB | ColumnC | Calculate count | Correct Calc count | Day of week | ||
7 | 21.9.2017 | 1000 | A | 1 | 1 | 5 | ||
7 | 22.9.2017 | 1000 | A | 6 | ||||
7 | 16.11.2017 | 1000 | A | 2 | 2 | 5 | ||
7 | 17.11.2017 | 1000 | A | 6 | Friday | |||
NEW LINE | 20.11.2017 | 1000 | A | 3 | 2 | Monday | ||
7 | 8.1.2018 | 1000 | A | 4 | 3 | 2 | ||
7 | 9.1.2018 | 1000 | A | 3 | ||||
7 | 12.1.2018 | 1000 | A | 5 | 4 | 6 | ||
7 | 1.6.2018 | 1000 | A | 6 | 5 | 6 | ||
7 | 3.10.2018 | 1000 | A | 7 | 6 | 4 | ||
7 | 4.10.2018 | 1000 | A | 5 | ||||
7 | 3.1.2019 | 1000 | A | 8 | 7 | 5 | ||
7 | 11.2.2019 | 1000 | A | 9 | 8 | 2 | ||
7 | 12.2.2019 | 1000 | A | 3 |
I tried to exclude the holidays and weekends from my calculations but I'm not getting the desired "correct" output
I think you just need to change the 'AddCol1' formula to:
AddedCol1 = var subDays = IF( Data[Day of Week] = 2, 3, 1 ) return ISEMPTY( CALCULATETABLE( Data, ALLEXCEPT( Data, Data[ColumnB], Data[ColumnC] ), Data[Date] = EARLIER( Data[Date] ) - subDays ) ) + 0
Did I answer your question correctly? Mark my answer as a solution!
Proud to be a Datanaut!
That could work well in case of mondays. However, I'm dealing with holidays too !
I have a Holiday table merged to my datetable so all holiday rows are marked with 0 and 1 if their active. I basically need to count the days between the last active day and current active day and use that variable for the subDays
Hi @Yggdrasill
are you able to post some sample data with your date table included?
Did I answer your question correctly? Mark my answer as a solution!
Proud to be a Datanaut!
Hi @Yggdrasill
you can do thi sin 2 steps:
first add this column:
AddedCol1 = ISEMPTY( CALCULATETABLE( Data, ALLEXCEPT( Data, Data[ColumnB], Data[ColumnC] ), Data[Date] = EARLIER( Data[Date] ) - 1 ) ) + 0
and then this final column:
AddedCol2 = IF( Data[AddedCol1] = 0, BLANK(), CALCULATE( SUM( Data[AddedCol1] ), ALLEXCEPT( Data, Data[ColumnC], Data[ColumnB] ), Data[Date] <= EARLIER( Data[Date] ) ) )
Did I answer your question correctly? Mark my answer as a solution!
Proud to be a Datanaut!
You absolute beauty !
Thank you !
User | Count |
---|---|
94 | |
83 | |
78 | |
75 | |
66 |
User | Count |
---|---|
115 | |
105 | |
93 | |
65 | |
62 |