Issues with Calculation Groups - Cumulative Total and Formatting

[lingleji]

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!

[Anonymous]

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