Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hi All,
I am trying to work out a solution for dynamic filtering of Last N Months of Cumulative Transaction Amount. What I wanted is to select the number of months from the Last N Months filter (created via parameter) based on the date selected from another filter (which is a disconnected date table of the distinct date from the fact table).
I was able to create a mesure for the Total Transsaction Amount and it is giving me the exact result i wanted. But i cannot make it work for the Cumulative Transaction Amount as it is basically returning the same output from the Total Transaction Amount.
What i wanted is instead of returning the values in red, I want the values next to it (i.e. $324,835 instead of -$82,014, $385,398 instead of -$21,451 and so on). I also want the value in Apr-2021 to be blank.
Below is the DAX measure:
Cumulative Transaction Amount Last N Months =
Solved! Go to Solution.
// Because your model is not structured correctly
// with a proper date table, you pay the price
// by having a complex calculation of the previous
// date. You've got yourself to blame for that.
Cumulative Transaction Amount Last N Months =
var MonthsBack = [Last N Months Value]
VAR CurrentDate = SELECTEDVALUE( DateFilter[Date] )
var CurrentYear = Year( CurrentDate )
var CurrentMonth = Month( CurrentDate )
var PreviousYear_ =
CurrentYear
- CEILING(
DIVIDE(
MAX( 0, MonthsBack - CurrentMonth ),
12
),
1
)
var PreviousMonth_ =
1 + MOD(
CurrentMonth - MonthsBack,
12
)
VAR PreviousDate =
DATE(
PreviousYear_,
PreviousMonth_,
1
)
VAR Result =
CALCULATE(
[Cumulative Transaction Amount],
PreviousDate <= TransactionTbl[Date],
TransactionTbl[Date] <= CurrentDate,
// This last line must be here if the column
// comes from TransactionTbl. If it comes from
// the disconnected table, you don't have to
// add it.
REMOVEFILTERS( TransactionTbl[MonthYear] )
)
RETURN
Result
Even though the measure above works, it's brittle because your model is incorrect. I'd strongly suggest you create a good model so that time-based calculations can be performed using the standard means: a proper date table and the time-intel functions. This will also remove the complexity from the above measure.
Hi,
I am not sure of what you want but see if this article on my site helps - Flex a Pivot Table to show data for x months ended a certain user defined month.
Thanks a lot for the feedback Ashish. I found a solution myself i will read the aricle.
Cheers
LarsAustin
// Because your model is not structured correctly
// with a proper date table, you pay the price
// by having a complex calculation of the previous
// date. You've got yourself to blame for that.
Cumulative Transaction Amount Last N Months =
var MonthsBack = [Last N Months Value]
VAR CurrentDate = SELECTEDVALUE( DateFilter[Date] )
var CurrentYear = Year( CurrentDate )
var CurrentMonth = Month( CurrentDate )
var PreviousYear_ =
CurrentYear
- CEILING(
DIVIDE(
MAX( 0, MonthsBack - CurrentMonth ),
12
),
1
)
var PreviousMonth_ =
1 + MOD(
CurrentMonth - MonthsBack,
12
)
VAR PreviousDate =
DATE(
PreviousYear_,
PreviousMonth_,
1
)
VAR Result =
CALCULATE(
[Cumulative Transaction Amount],
PreviousDate <= TransactionTbl[Date],
TransactionTbl[Date] <= CurrentDate,
// This last line must be here if the column
// comes from TransactionTbl. If it comes from
// the disconnected table, you don't have to
// add it.
REMOVEFILTERS( TransactionTbl[MonthYear] )
)
RETURN
Result
Even though the measure above works, it's brittle because your model is incorrect. I'd strongly suggest you create a good model so that time-based calculations can be performed using the standard means: a proper date table and the time-intel functions. This will also remove the complexity from the above measure.
Hi Daxer. Thanks so much for your feedback and for taking time to look for a solution to my problem. I actually found the solution (Measure details below). The key is the use of SELECTEDVALUE. It is not as elegant (and maybe not as efficient) as the solution you came up with but it works.
I also made changes to the PreviousMonth variable by using EDATE rather than DATE.
Thanks
LarsAustin
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
117 | |
104 | |
69 | |
69 | |
43 |
User | Count |
---|---|
146 | |
106 | |
104 | |
89 | |
65 |