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
AmalrajRRD1
Helper II
Helper II

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)

Capture.PNG

1 ACCEPTED SOLUTION
Anonymous
Not applicable

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.

View solution in original post

4 REPLIES 4
Anonymous
Not applicable

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.

Anonymous
Not applicable

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

Anonymous
Not applicable

Been a while since I wrote the post, so i'm not sure why it says END.  That should be RETURN.

Thanks . It's working fine

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.