Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

The Power BI DataViz World Championships are on! With four chances to enter, you could win a spot in the LIVE Grand Finale in Las Vegas. Show off your skills.

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
Feb2025 Sticker Challenge

Join our Community Sticker Challenge 2025

If you love stickers, then you will definitely want to check out our Community Sticker Challenge!

Jan NL Carousel

Fabric Community Update - January 2025

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