cancel
Showing results for 
Search instead for 
Did you mean: 
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))



Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!
Dashboard of My Blogs !! YouTube Channel !! Connect on Linkedin

Proud to be a Super User!

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
PBI_User Group Leader_768x460.jpg

Manage your user group events

Check out the News & Announcements to learn more.

Welcome Super Users.jpg

Super User Season 2

Congratulations, the new Super User Season 2 for 2021 has started!

Community Connections 768x460.jpg

Community & How To Videos

Check out the new Power Platform Community Connections gallery!