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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

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
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

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

Top Solution Authors