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 (
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],
'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"))),
'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.
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!