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,
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
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 |
---|---|
107 | |
98 | |
78 | |
65 | |
53 |
User | Count |
---|---|
144 | |
104 | |
100 | |
86 | |
64 |