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
LarsAustin
Helper I
Helper I

Show Cumulative Total Last N Months from Selected Date

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.

 

LarsAustin_0-1618184696710.png

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 =

VAR CurrentDate = MAX(DateFilter[Date])
VAR PreviousDate = DATE(YEAR(CurrentDate),MONTH(CurrentDate)-[Last N Months Value]+1,DAY(CurrentDate))
VAR Result = CALCULATE([Cumulative Transaction Amount],FILTER(ALL(TransactionTbl[Date]),TransactionTbl[Date]>=PreviousDate && TransactionTbl[Date]<=CurrentDate))
RETURN
Result
 
I also attached the PBIX.
 
 
I have tried most of the solutions from similar post but i cannot make it work. I will appreciate any help.
 
Thanks
 
LarsAustin

 

1 ACCEPTED SOLUTION
daxer-almighty
Solution Sage
Solution Sage

 

 

// 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.

 

 

View solution in original post

4 REPLIES 4
Ashish_Mathur
Super User
Super User

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.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

Thanks a lot for the feedback Ashish. I found a solution myself i will read the aricle.

 

Cheers

 

LarsAustin

daxer-almighty
Solution Sage
Solution Sage

 

 

// 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.

 

Cumulative Transaction Amount Last N Months =
VAR CurrentDate = MAX(DateFilter[Date])
VAR PreviousDate = EDATE(CurrentDate,-([Last N Months Value]-1))
VAR Result = CALCULATE([Cumulative Transaction Amount],FILTER(ALL(TransactionTbl[Date]),SELECTEDVALUE(TransactionTbl[Date])>=PreviousDate && SELECTEDVALUE(TransactionTbl[Date])<=CurrentDate))
RETURN
Result

 

Thanks

 

LarsAustin

 

 

 

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.