Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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
Solved! Go to 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.
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.
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.
Good Evening Jihwan.
Your solution perfect! Exactly what I wanted.
Thanks so much for looking into my issue and solving it.
Cheers
LarsAustin
@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
User | Count |
---|---|
128 | |
109 | |
100 | |
65 | |
62 |
User | Count |
---|---|
137 | |
115 | |
102 | |
71 | |
61 |