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
Jeremyh
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

 

Jeremyh_0-1626065328799.png

 

2 REPLIES 2
amitchandak
Super User
Super User

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

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

Jeremyh_0-1631592839977.png

 

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

Jeremyh_1-1631592839987.png

 

 

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

Jeremyh_2-1631592839999.png

 

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

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.