DAX to get the last month data for fiscal year

[Apple08]

Hi All

The last month of the fiscal year is July, however I only have data for the full year of FY19/20 and FY20/21, the data in FY21/22 is only up to Jun-22.  I need to create a dynamic line chart to compare the last month of data of each fiscal year which is July-20, July-21 and the maximum month in the current financial year.

I have put in the DAX to count the number of people in different age group below:

YTD Age Group = CALCULATE([Count Age Group],filter(Dates,'Dates'[Fiscal Month]=max(Dates[Fiscal Month])))

The formula can pick the data in July-20, July-21, however it is showing as blank for FY21/22 because there is no data in July-22 yet.  I wonder is there any way I can update the DAX to capture the maximum month data for the current financial year, in this example it would be Jun-22 instead.  Many thanks for any help in advance.

[NickA01]

Thanks for the sample. 
Having a look now. 

Just to check my understanding , you want the Green cells in the below ?

[Apple08]

Hi Nick, yes this is correct.  The report should be able to captured all previous value in the month July and the maximum month of data in the current fiscal year.

[NickA01 · Accepted solution]

Okay, think we are there..  (Fingers crossed)  ***  Also please mark your Dates table as a date table 

YTD Age Group 3 =

var Max_AG_date = MAX('Staff Data'[As at Date])

--return Max_AG_date

var FiscalMth = Calculate (Max(Dates[Fiscal Month]),

                            Filter(Dates,'Dates'[Dates]=Max_AG_date)

)

return

CALCULATE([Count Age Group],

             filter(Dates,'Dates'[Fiscal Month]=FiscalMth)

            )

Here's test output

[Apple08]

Wow it works perfectly!  Thanks very much NickA01 for your time and effort to help, I appreciated it very very much! 🙂

[NickA01]

Greetings. 

Assuming your [Dates] table it a date table and your Age Group data is in another table, these 2 joining on date key: 
You have multiple options. 

1:  -- Simple dax
CALCULATE([Count Age Group],
filter(Dates,'Dates'[datekey]<=MAX('AgeGroupTable'[datekey])

2: -- use a variable
YTD Age Group = 
var Max_AG_dataKey = MAX('AgeGroupTable'[datekey])
return
CALCULATE([Count Age Group],
             filter(Dates,'Dates'[datekey]<=Max_AG_dataKey)
             )

3:  -- This options comes in useful if your date table is joined to several other tables. 
Add a new colum to the data table 
EG:  HasAgeGrpData = If(Isblank
                                                  (Lookupvalue(AgeGroupData.DateKey,AgeGroupData.DateKey,Dates.datekey)
                                        ,"No,Yes")

* This may need a firstNonBlank or additional Lookup filters is there are multiple rows with the same datekey in the AgeGroupData table. 

Once you have this additional column working, you can just use it as a filter in your original dax. 
YTD Age Group = CALCULATE([Count Age Group],filter(Dates,'Dates'[HasAgeGrpData]="Yes"))

Hoper this helps\ gives you some pointers. 

The Nick

If the post helps please give a thumbs up || If it solves your issue, please accept it as the solution to help the other members find it more quickly.

If it's the biggest heap of stinky smelly stuff then I'm sorry .

[Apple08]

Thanks The Nick.

I just want to get the value for the last month of the financial year, so only the values in July and the maximum month in the current financial year.  The DAX above has returned the total sum of the whole financial year data.

Please see my file at: 

https://drive.google.com/file/d/1OduRIB5JNbfLykqE5tZv3MvAzJ4gJOEp/view?usp=sharing

I have created a measure 'YTD Age Group 2' with the DAX suggested, however it returned the total sum of the whole year age group count rather than just the last month July.  Many thanks.