Forum Discussion
Latest Month value
Hi All,
How to show my latest month value in my card.
Note: Please find the below image for my hole month value amount but i want to show latest month (exam: Mar 2018)
You'll need to write a measure to handle this.
Here is a simple way to do it:
Use a calculated column to get your month date. This would be something like:
MonthDate = DATE(YEAR([DateField]), MONTH([DateField]), 1)
where [DateField] is the name of the column that holds the finacial details date.
Next create a measure that does this:
Last Month Sum = VAR endDate = LASTDATE('YourTable'[MonthDate]) END CALCULATE( SUM('YourTable'[BudgetValue]), 'YourTable'[MonthDate] = endDate )Please note that this measure will take into consideration the context of your report filters. If you use a Date slicer and contrain the report, the last month will always been the last month within your Date contraints.
4 Replies
You'll need to write a measure to handle this.
Here is a simple way to do it:
Use a calculated column to get your month date. This would be something like:
MonthDate = DATE(YEAR([DateField]), MONTH([DateField]), 1)
where [DateField] is the name of the column that holds the finacial details date.
Next create a measure that does this:
Last Month Sum = VAR endDate = LASTDATE('YourTable'[MonthDate]) END CALCULATE( SUM('YourTable'[BudgetValue]), 'YourTable'[MonthDate] = endDate )Please note that this measure will take into consideration the context of your report filters. If you use a Date slicer and contrain the report, the last month will always been the last month within your Date contraints.
I must be missing something. When I attempt this answer I am getting errors. I can get the "MonthDate" column to work fine. But when I try to copy and paste the other code in for a new measure to get the data I get all sorts of errors stating the DAX is incorrect. Can you tell me what I"m missing?
It doesn't matter if I enter in my table name/values in the equation. The error doesn't change.
The syntax for 'END' is incorrect. (DAX(VAR endDate = LASTDATE('DIMCalendar'[MonthDate])ENDCALCULATE( SUM('YourTable'[BudgetValue]), 'YourTable'[MonthDate] = endDate))).
Thanks . It's working fine
