Skip to main content
cancel
Showing results for 
Search instead 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

Reply
sabilahmed
Resolver I
Resolver I

How to make current month revenue zero or blank?

Hi,

 

I have the below Matrix viz:

sabilahmed_1-1660681267270.png

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
sabilahmed
Resolver I
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())))

View solution in original post

5 REPLIES 5
daXtreme
Solution Sage
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

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

sabilahmed
Resolver I
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())))

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.

Hi @daXtreme 

 

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

 

Thanks,

Sabil

Helpful resources

Announcements
Europe Fabric Conference

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 Carousel June 2024

Power BI Monthly Update - June 2024

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

PBI_Carousel_NL_June

Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.