Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register Now

Reply
lingleji
Frequent Visitor

Issues with Calculation Groups - Cumulative Total and Formatting

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])

 

 

Cumulative Actuals - Working.PNG

 

Cumulative Actuals.PNG

 

 

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.

Formatting.PNG

 

 

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%")    )

 

 

Var vs PY with Formatting.PNG

 

Var vs PY without Formatting.PNG

 

Any assistance is greatly appreciated.  Thanks in advance!

1 REPLY 1
Anonymous
Not applicable

Hi @lingleji ,

 

ShundaSteph_1-1660026299027.png

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

Helpful resources

Announcements
Fabric Community Conference

Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

February 2024 Update Carousel

Power BI Monthly Update - February 2024

Check out the February 2024 Power BI update to learn about new features.

Fabric Career Hub

Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

Fabric Partner Community

Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.