Dynamic N Period Slicer - Not Showing Value of Period Where There is No Change from Previous

[LarsAustin]

Hi All,

This is related from a previous post I made yesterday which has been solved.

Below is the screenshot of my visual. This is perfectly fine and is giving me the outcome that I wanted.

The problem is when I filter an item from TransactionID (i.e. A1), there are periods which are showing blank.  What I need is to show $123,646 on Apr-2020 and $91,772 on Jun-2020 under Cumulative Transaction Amount Last N Months. It showing the same pattern when i select the other two TransactionID's (A2 and A3). What I noticed is that it is showing as blank when there is no change from the previous month.

\

Below is the measure I created for Cumulative Transaction Amount Last N Months:

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

I also attached the pbix file.

https://www.dropbox.com/s/7cak4m21g2vuy3o/Sample%20Transsaction%20Log%20PBI%20v2.pbix?dl=0

I will appreciate if someone can help me fix the issue and if you can explain to me what is going on and what i missed in my measure (and/or model).

Thank you.

LarsAustin

[Jihwan_Kim · Accepted solution]

Hi, @LarsAustin 

Please correct me if I wrongly understood your question.

Please try the below measure. I added one more condition to your measure.

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

Hi, My name is Jihwan Kim.

If this post helps, then please consider accept it as the solution to help other members find it faster, and give a big thumbs up.

[LarsAustin]

Good Evening Jihwan.

Your solution perfect! Exactly what I wanted.

Thanks so much for looking into my issue and solving it.

Cheers

LarsAustin

[amitchandak]

@LarsAustin , Cumulative or rolling (cumulative of few month) should be done with date table like

Cumm Sales = CALCULATE(SUM(Sales[Sales Amount]),filter(allselected('date'),'date'[date] <=max('date'[date])))

Cumm Sales till last month = CALCULATE(SUM(Sales[Sales Amount]),filter(allselected(date),date[date] <=maxx(date,dateadd(date[date]),-1,Month)))

Rolling 12 = CALCULATE(sum(Sales[Sales Amount]),DATESINPERIOD('Date'[Date ],MAX(Sales[Sales Date]),-12,MONTH))
Rolling 12 = CALCULATE(sum(Sales[Sales Amount]),DATESINPERIOD('Date'[Date ],MAX('Date'[Date ]),-12,MONTH))

Rolling 3= CALCULATE(sum(Sales[Sales Amount]),DATESINPERIOD('Date'[Date ],MAX(Sales[Sales Date]),-3,MONTH))
Rolling 3 = CALCULATE(sum(Sales[Sales Amount]),DATESINPERIOD('Date'[Date ],MAX('Date'[Date ]),-3,MONTH))

[LarsAustin]

Thanks Amit.

I know that the usual cumulative or rolling calculation should be done using a proper date table. I have a separate measure for that as below:

Cumulative Amount =

CALCULATE (

[Total Amount],

FILTER ( ALL ( 'Date' ), 'Date'[Dates] <= MAX ( 'Date'[Dates] ) )

)

My issue is because I wanted my filter to be dynamic to show a desired number of month values from a base date, I created another measure (Cumulative Last N Months) which is linked to a disconnected table (DateFilter). As I mentioned, it is perfectly (well, sort of) fine but an issue arises when i select a single item (TransactionId).

Thanks

LarsAustin