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

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

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.

 

LarsAustin_0-1618276574694.png

 

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.

 

LarsAustin_1-1618276719957.png\

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

1 ACCEPTED SOLUTION
Jihwan_Kim
Super User
Super User

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.

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


Go to My LinkedIn Page


View solution in original post

4 REPLIES 4
Jihwan_Kim
Super User
Super User

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.

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


Go to My LinkedIn Page


Good Evening Jihwan.

 

Your solution perfect! Exactly what I wanted.

 

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

 

Cheers

 

LarsAustin

amitchandak
Super User
Super User

@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))

 

 

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

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.