Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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 , 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
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
User | Count |
---|---|
140 | |
113 | |
104 | |
76 | |
63 |
User | Count |
---|---|
135 | |
126 | |
110 | |
70 | |
61 |