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.
Hi super users. I'm having a couple of issues putting the finishing touches on something with calculation groups. I've run into 2 issues.
1. I've got a cumulative total calculation that works as a measure, but when I try to use it (even modified), it isn't returning the correct values.
This measure is working.
Cumulative Revenue =
VAR MonthStart = DISTINCT (
SELECTCOLUMNS (
FILTER('Fiscal Calendar', 'Fiscal Calendar' [Calendar_Date] = Today()-1) ,
"Fiscal Calendar", [Month_Start_Date]
))
This is what I've tried using in the Calculation Group for Cumulative Revenue, but it just returns the total for the current day.
RETURN CALCULATE ([Total Revenue],
FILTER(ALLSELECTED('Collected Payments'),
'Collected Payments'[Invoice Closed Date] <= MAX('Collected Payments'[Invoice Closed Date])
CALCULATE (CALCULATE(SUM('Collected Payments'[Amount]), FILTER('Collected Payments', 'Collected Payments'[Category] <> "Tips" && ('Collected Payments'[Payment Type] = "Card" || 'Collected Payments'[Payment Type] = "Cash" || 'Collected Payments'[Payment Type] = "Check" || 'Collected Payments'[Payment Type] = "Custom"))),
FILTER(ALLSELECTED('Fiscal Calendar'),
'Fiscal Calendar'[Calendar_Date] <= MAX( 'Fiscal Calendar'[Calendar_Date])
2. I've got a variance calculation in the calculation group for Var vs PY %. Here is the DAX that I'm using. When I don't include the FORMAT as % , it correctly is hidden (I don't have prior year data). However, when I add it to format the values that do return a value, it returns 0% for the Last Year (which has no prior year actuals to compare to so should not be returned). I saw another couple of posts that mention changing the Data Type and Format, but it is not allowing me to convert from Text.
With formatting
VAR Act = SELECTEDMEASURE()Var Targ = CALCULATE(SELECTEDMEASURE(), 'CG_Select total'[Select Total]="Target")VAR CurrentMeasure = SELECTEDMEASURE ()VAR PriorMeasure = CALCULATE ( SELECTEDMEASURE (), 'Time Intelligence'[Years] = "Last Year" )RETURN IF ( CurrentMeasure = BLANK () || Targ = BLANK (), BLANK (), FORMAT(((CurrentMeasure - Targ) / Targ), "0%") )
Any assistance is greatly appreciated. Thanks in advance!
Hi @lingleji ,
May I ask what value does this formula correspond to in your screenshot?
And you may try to use DIVDE() function for division. It returns alternate result or BLANK() on division by 0.
DIVIDE function (DAX) - DAX | Microsoft Docs
Best Regards,
ShundaSteph
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 |
---|---|
109 | |
99 | |
77 | |
66 | |
54 |
User | Count |
---|---|
144 | |
104 | |
102 | |
87 | |
64 |