topic Re: How to make current month revenue zero or blank? in DAX Commands and Tips

How to make current month revenue zero or blank?

sabilahmed — Tue, 16 Aug 2022 20:27:41 GMT

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 🙂

Re: How to make current month revenue zero or blank?

sabilahmed — Tue, 16 Aug 2022 22:47:14 GMT

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

Re: How to make current month revenue zero or blank?

daXtreme — Tue, 16 Aug 2022 22:53:10 GMT

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

Re: How to make current month revenue zero or blank?

daXtreme — Tue, 16 Aug 2022 22:58:50 GMT

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.

Re: How to make current month revenue zero or blank?

sabilahmed — Tue, 16 Aug 2022 23:00:48 GMT

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

Re: How to make current month revenue zero or blank?

sabilahmed — Wed, 17 Aug 2022 07:25:44 GMT

Hi daXtreme

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

Thanks,

Sabil