Forum Discussion
Dax error
hello Diportee97
i might be wrong but i think [Avg Satisfaction pm] and [Avg Wait Time pm] are taking value from wrong measure.
as you can see below, those pm value have all same value because they are using same exact DAX and same measure.
[Patient Visit pm] is using [Patient Visit] which i think this is correct
[Avg Satisfaction pm] is using [Patient Visit] instead of using [Avg Satisfaction Score]
[Avg Wait Time pm] is using [Patient Visit] instead of using [Avg Wait Time]
if i change the measure to the corresponding measure, i think the value looks more sensible.
Hope this will help.
Thank you.
Hey there!
If I understood correctly, you are calculating previous month percentage changes for your cards, but the second and third cards are displaying incorrect percentages, such as -97% instead of expected values like 2%, 4%, 5%-10%.
Here are some new DAX formulas for you to try:
1. Measure for Previous Month Value:
PreviousMonthValue =
CALCULATE(
SUM('Table'[MetricColumn]),
PREVIOUSMONTH('DateTable'[Date])
)
2. Measure for Percentage Change:
PercentageChange =
VAR CurrentValue = SUM('Table'[MetricColumn])
VAR PrevValue = CALCULATE(SUM('Table'[MetricColumn]), PREVIOUSMONTH('DateTable'[Date]))
RETURN
IF(
ISBLANK(PrevValue) || PrevValue = 0,
BLANK(), -- Avoid dividing by zero
DIVIDE(CurrentValue - PrevValue, PrevValue, 0)
)
Hopefully these formulas will work because it will handle division by zero which ensures previous month values exist before calculating percentages. It will correctly retrieves previous month values by using PREVIOUSMONTH() with a properly structured Date Table. And it fixes incorrect signs (e.g., -97%) by ensuring logical calculations.
Hope this helps!
😁😁
I don't really understand your Dax and I don't think it really tally with my already established Dax,I would love if you can view and check the pbix file i sent earlier
hello Diportee97
i might be wrong but i think [Avg Satisfaction pm] and [Avg Wait Time pm] are taking value from wrong measure.
as you can see below, those pm value have all same value because they are using same exact DAX and same measure.
[Patient Visit pm] is using [Patient Visit] which i think this is correct
[Avg Satisfaction pm] is using [Patient Visit] instead of using [Avg Satisfaction Score]
[Avg Wait Time pm] is using [Patient Visit] instead of using [Avg Wait Time]
if i change the measure to the corresponding measure, i think the value looks more sensible.
Hope this will help.
Thank you.