Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
Hi,
I have a transaction table (that is connected with Business Central) which has the following relevant columns among others: Posting Date col, the name of the associates, Category, and a column with number of hours worked. I have been trying to write a measure through which I want sum the number of worked hours if date is after December 1, 2021 and the category columns are filtered based on certain conditions. Here is what I came up with:
Solved! Go to Solution.
Thansk for your feedback @AlexisOlson.
I tried your code and even though it worked, it gave me a different result that I could not really verify. However, when I realized that the filtering of the Poste_Date column in my transaction table is no different than the other filterings then I wrote the following and got the right result:
CALCULATE(
SUM(
'Ärendetransaktioner_Excel'[Quantity]
),
'Ärendetransaktioner_Excel'[Entry_Type] ="usage",
'Ärendetransaktioner_Excel' [Type] = "Resource",
'Ärendetransaktioner_Excel'[Chargeable_PGS] =TRUE(),
'Ärendetransaktioner_Excel'[Posting_Date] >= DATE(2021,12,01)
)
🙂
The basic problem appears to be mismatching parentheses.
Instead of this
MAX ( Excel[Posting_Date] >= DATE ( 2021, 12, 01 ) )
I think you want this:
MAX ( Excel[Posting_Date] ) >= DATE ( 2021, 12, 01 )
assuming [Posting_Date] is a table column and not a measure.
Another issue is that IF needs to return the same data type for both branches, not a numeric quantity in some cases, and text "-" for others.
Try this:
Total Worked Hours =
IF (
MAX ( 'Ärendetransaktioner_Excel'[Posting_Date] ) >= DATE ( 2021, 12, 01 ),
CALCULATE (
SUM ( 'Ärendetransaktioner_Excel'[Quantity] ),
KEEPFILTERS ( 'Ärendetransaktioner_Excel'[Entry_Type] = "usage" ),
KEEPFILTERS ( 'Ärendetransaktioner_Excel'[Type] = "Resource" ),
KEEPFILTERS ( 'Ärendetransaktioner_Excel'[Chargeable_PGS] = TRUE () )
),
0
)
Thansk for your feedback @AlexisOlson.
I tried your code and even though it worked, it gave me a different result that I could not really verify. However, when I realized that the filtering of the Poste_Date column in my transaction table is no different than the other filterings then I wrote the following and got the right result:
CALCULATE(
SUM(
'Ärendetransaktioner_Excel'[Quantity]
),
'Ärendetransaktioner_Excel'[Entry_Type] ="usage",
'Ärendetransaktioner_Excel' [Type] = "Resource",
'Ärendetransaktioner_Excel'[Chargeable_PGS] =TRUE(),
'Ärendetransaktioner_Excel'[Posting_Date] >= DATE(2021,12,01)
)
🙂
@Anonymous , Try like
Total Worked Hours =
IF(
MAX('Ärendetransaktioner_Excel'[Posting_Date]) >= DATE(2021,12,01) ,
CALCULATE(
SUM(
'Ärendetransaktioner_Excel'[Quantity]
),
FILTER( 'Ärendetransaktioner_Excel', 'Ärendetransaktioner_Excel'[Entry_Type] ="usage" && 'Ärendetransaktioner_Excel' [Type] = "Resource" && 'Ärendetransaktioner_Excel'[Chargeable_PGS] =TRUE() )
)
)
Thanks for your quick response. && makes the code cleaner, but is not solving the problem with the first argument of my IF statement. Still get error message!
User | Count |
---|---|
140 | |
113 | |
104 | |
77 | |
65 |
User | Count |
---|---|
135 | |
116 | |
101 | |
71 | |
61 |