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 am able to calculate the difference between two columns, but am not able to count those results. I have a single table that list values and I need to count the number of increases and decreses, beyond a %, within a time period. I am able to Return "Diff" to a table visual and I need to cont those values as below.
Diff30CTest =
VAR First30W =
CALCULATE(
MIN(AllWeights[Date]),
FILTER(
AllWeights,
AllWeights[Date] >= [sDateLess30] &&
AllWeights[Date] <= [selectDate] &&
AllWeights[ClientId] = SELECTEDVALUE(AllWeights[ClientId])
)
)
VAR FirstValue =
CALCULATE(
MIN(AllWeights[Value]),
FILTER(
AllWeights,
AllWeights[ManPK] = MINX(
FILTER(
AllWeights,
AllWeights[Date] = First30W &&
AllWeights[ClientId] = SELECTEDVALUE(AllWeights[ClientId])
),
AllWeights[ManPK]
)
)
)
VAR Last30W =
CALCULATE(
MAX(AllWeights[Date]),
FILTER(
AllWeights,
AllWeights[Date] >= [sDateLess30] &&
AllWeights[Date] <= [selectDate] &&
AllWeights[ClientId] = SELECTEDVALUE(AllWeights[ClientId])
)
)
VAR LastValue =
CALCULATE(
MAX(AllWeights[Value]),
FILTER(
AllWeights,
AllWeights[ManPK] = MAXX(
FILTER(
AllWeights,
AllWeights[Date] = Last30w &&
AllWeights[ClientId] = SELECTEDVALUE(AllWeights[ClientId])
),
AllWeights[ManPK]
)
)
)
VAR CompareFisrtToLast = IF([Last30W] <> [First30W],DIVIDE(([Last30W]-[First30W]),[First30W]),BLANK())
VAR Diff = If(ISBLANK([First30W]),BLANK(),CompareFisrtToLast)
VAR DiffAbove5Percent = IF(Diff > 0.05, 1, 0)
VAR DiffBelowNeg5Percent = IF(Diff < -0.05, 1, 0)
RETURN
I have tried bothe CountX and SUMX ..
So I think that my issue is that "Diff" is returning a rows and I am trying to count those, but DAX expect to count the rows of a table.
HI @nabullock,
According to your description, it seems like common multiple date fields analysis requirement. For this scenario, you can take a look at the Greg’s blog ‘start date’, ‘end date’ part if helps:
Before You Post, Read This: start/end date
Regards,
Xiaoxin Sheng
I don't believe that it is an issue with the dates becuse I am able to correctly show value differents (DIFF30) between the first value (First30W) and the last value (Last30W) within the date range. I just need to count those. If I needed to count those that were +/- 4%, in the table below, it would be 3, 2 are a loss and there is 1 gain.
Hi @nabullock,
Can you please share a pbix or some dummy data that keep the raw data structure with expected results? It should help us clarify your scenario and test to coding formula.
How to Get Your Question Answered Quickly
Regards,
Xiaoxin Sheng
ManPKClientIdDateValue
|
Trying to count the loss and gain columns which all values come from this measure because the date is selected by the user
------------------------------------------
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 |
---|---|
49 | |
27 | |
20 | |
15 | |
12 |
User | Count |
---|---|
58 | |
50 | |
44 | |
19 | |
18 |