How to make current month revenue zero or blank?

[sabilahmed]

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 🙂

[daXtreme]

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

[sabilahmed]

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

[sabilahmed · Accepted solution]

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

[daXtreme]

Hi @sabilahmed 

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.

[sabilahmed]

Hi @daXtreme 

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

Thanks,

Sabil