cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

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

Announcements

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

Power BI Monthly Update - August 2024

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

Fabric Community Update - August 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors