cancel
Showing results for
Did you mean:
Frequent Visitor

## Fiscal Year to Date Table calculating to Completed Fiscal Month

Hello.

Can someone please help me with a Solution to generate a Fiscal Month To Date Table for a Fiscal/Financial Year Beginning in July (Fiscal Year = 1 July to 30 June)

I only want to calculate up to the end of the last full Month and populate a table as follows without updating filters month to month

Example, if the Current Date was Mid June, I’d want to return the table shown in the picture. I have defined Fiscal Month Number in my Date/Calendar Table (July = 1, June = 12) and have set Sort Order by this to produce the this table

2 REPLIES 2
Super User IV

@Jeremyh , Try a measure like

Meausre =
var _max = if(today() = eomonth(today(),0) , today(), eomonth(today(),1-) )
var _year = if(month(_max) <=6, year(today()) -1,year(today()))
var _min = date(year,7,1)
return
calculate(Sum(Table[Value]), filter(Table, Table[Date] >=_min & Table[Date] <=Max))

Proud to be a Super User!

Frequent Visitor

Thank you for your reply but it's not quite what I'm looking for. I’ve had a bit of time to rethink the approach with a clearer description for what I’m trying to achieve.

Now that I am 2 Whole Months into the new Fiscal Year (Beginning July 1st) it’s easier to show what I want.

I already have a Date Calendar with the Number of the Fiscal Month. For Example, September is the 3rd Fiscal Month

What I want is a simple Dax Statement for the [Latest Fiscal Month] so that I can use the in this following DAX

So that I can get my table to not calculate results Greater than the current Fiscal Month. Eg.

Any Help with this would be very much appreciated. Thank you

Announcements

#### Microsoft named a Leader in The Forrester Wave

Microsoft received the highest score of any vendor in both the strategy and current offering categories.