cancel
Showing results for
Did you mean:

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

Resolver I

## How to make current month revenue zero or blank?

Hi,

I have the below Matrix viz:

As you can see, Agreements is generating a revenue SUM for Aug (the current month), but I want this to show as blank. What changes can I make to the DAX?

``TOTAL_RS1 = SUM(Agreement_Billing_Report[Extended Price])``

The 'Agreement_Billing_Report' table is connected to a Dim_Calendar table, which is how I create this Matric viz. I also have a Rev_Type connected to this table to reference the Revenue Type in the rows, but this is not important for this exercise.

Your help would be most appreciated. Thanks 🙂

1 ACCEPTED SOLUTION
Resolver I
``Agreement Total = CALCULATE(SUMX('Agreement_Billing_Report',IF(DATEDIFF('Agreement_Billing_Report'[Date of Invoice],TODAY(),MONTH) >= 1,'Agreement_Billing_Report'[Extended Price], BLANK())))``
5 REPLIES 5
Solution Sage
``````TOTAL_RS1 =
var Today_ = TODAY()
var CurrentMonthID =
CALCULATE(
// MonthId must be unique across all the years.
// And no, it's not the number of the month
// in a year. It must be a true ID that is increasing
// by 1 from the very first month in the calendar
// to the very last. Such a field should be hidden
// in the UI as it's only a helper for measures.
SELECTEDVALUE( Dim_Calendar[MonthID] ),
Dim_Calendar[Date] = Today_,
REMOVEFILTERS( Dim_Calendar )
)
var Result =
CALCULATE(
SUM( Agreement_Billing_Report[Extended Price] ),
// One has to aggregate across all months in
// the context excluding the current month and
// all that succeed it. I add KEEPFILTERS just
// in case you select MonthID in the UI for, say,
// debugging puropses. But this field should not
// be able to be selected by the end user.
KEEPFILTERS( Dim_Calendar[MonthID] < CurrentMonthID )
)
return
Result``````
Resolver I

@daXtreme So I don't get it, are you saying my solution is incorrect? Because it seems to be working for me.

Resolver I
``Agreement Total = CALCULATE(SUMX('Agreement_Billing_Report',IF(DATEDIFF('Agreement_Billing_Report'[Date of Invoice],TODAY(),MONTH) >= 1,'Agreement_Billing_Report'[Extended Price], BLANK())))``
Solution Sage

One comment on your formula... If Agreement_Billing_Report is a huge table, the measure will be extremely slow as you're iterating it line by line doing computations on each of them. On the other hand, the formula I posted should be very fast even on very big tables. Try to compare the execution times of the two measures.

Resolver I

Excellent point. I'll check this and let you know how it goes.

Thanks,

Sabil