Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
Hi All,
I want to get a amount of last 2 year of current month.
Means Sum(amount) where year = Last 2 year and month = Current month.
Example:
Year Amount
2018 100$ // Amount of June 2018
2017 200$ // Amount of June 2017
Thank You,
Narender
Solved! Go to Solution.
Hi @Narender,
I modify my formula and current it only return this year and previous year records:
measure = VAR _currentDate = MAX ( 'Table'[Date] ) RETURN IF ( YEAR ( _currentDate ) IN { YEAR ( TODAY () ) - 1, YEAR ( TODAY () ) }, CALCULATE ( SUM ( 'Table'[Amount] ), VALUES ( 'Table'[Date].[Year] ), 'Table'[Date].[monthNo] = MONTH ( TODAY () ) ) )
Regards,
Xiaoxin Sheng
HI @Narender,
You can use following measure to achieve your goal:
Measure = CALCULATE ( SUM ( 'Table'[Amount] ), FILTER ( ALL ( 'Table' ), 'Table'[Date].[Year] >= MAX ( 'Table'[Date].[Year] ) - 1 && 'Table'[Date].[Year] <= MAX ( 'Table'[Date].[Year] ) ), VALUES ( 'Table'[Date].[Month] ) )
Regards,
Xiaoxin Sheng
Tyanks Sheng.
But i dont need each month amount.
I need current month amount like month(today()) of last year (2017) and month(today()) of this year (2018).
So amount will change according to current month of current year and current month of last year.
like
year measure
2017 100$ // This is current month(June) amount of previous year(2017)
2018 200$ // This is current month(June) amount of current year (2018)
Thanks,
Narender
Hi @Narender,
I modify my formula and current it only return this year and previous year records:
measure = VAR _currentDate = MAX ( 'Table'[Date] ) RETURN IF ( YEAR ( _currentDate ) IN { YEAR ( TODAY () ) - 1, YEAR ( TODAY () ) }, CALCULATE ( SUM ( 'Table'[Amount] ), VALUES ( 'Table'[Date].[Year] ), 'Table'[Date].[monthNo] = MONTH ( TODAY () ) ) )
Regards,
Xiaoxin Sheng
Hi Xiaoxin ,
Thanks for your reply.
Your mean to say
"This Year current month and Previous year Current month records" ?
Thanks,
Narender
Hi @Narender,
Sharre the link from where i can download your PBI/Excel file. Also, show the expected result there
Hi @Narender,
Yes, current it only return current year and previous year based on today function.
Regards,
Xiaoxin Sheng
User | Count |
---|---|
140 | |
113 | |
104 | |
77 | |
65 |
User | Count |
---|---|
136 | |
117 | |
101 | |
71 | |
61 |