cancel
Showing results for
Did you mean:

Fabric is Generally Available. Browse Fabric Presentations. Work towards your Fabric certification with the Cloud Skills Challenge.

Helper IV

DAX to get the last month data for fiscal year

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.
1 ACCEPTED SOLUTION
Resolver II

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

6 REPLIES 6
Resolver II

Thanks for the sample.
Having a look now.

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

Helper IV

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.

Resolver II

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

Helper IV

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

Resolver II

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 .

Helper IV

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.

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.

Announcements

Power BI Monthly Update - November 2023

Check out the November 2023 Power BI update to learn about new features.

Fabric Community News unified experience

Read the latest Fabric Community announcements, including updates on Power BI, Synapse, Data Factory and Data Activator.

The largest Power BI and Fabric virtual conference

130+ sessions, 130+ speakers, Product managers, MVPs, and experts. All about Power BI and Fabric. Attend online or watch the recordings.

Top Solution Authors
Top Kudoed Authors