Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started
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 🙂
Solved! Go to Solution.
Agreement Total = CALCULATE(SUMX('Agreement_Billing_Report',IF(DATEDIFF('Agreement_Billing_Report'[Date of Invoice],TODAY(),MONTH) >= 1,'Agreement_Billing_Report'[Extended Price], BLANK())))
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.
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.
Check out the September 2024 Power BI update to learn about new features.
Learn from experts, get hands-on experience, and win awesome prizes.
User | Count |
---|---|
25 | |
20 | |
18 | |
18 | |
15 |
User | Count |
---|---|
36 | |
19 | |
19 | |
17 | |
11 |