Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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:
Solved! Go to Solution.
Okay, think we are there.. (Fingers crossed) *** Also please mark your Dates table as a date table
Thanks for the sample.
Having a look now.
Just to check my understanding , you want the Green cells in the below ?
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.
Okay, think we are there.. (Fingers crossed) *** Also please mark your Dates table as a date table
Wow it works perfectly! Thanks very much NickA01 for your time and effort to help, I appreciated it very very much! 🙂
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 .
Hello!
I'm in a very similar situation. I used your script "1: -- simple Dax" and it worked perfectly for me. Now what I would like to be able to do is do the same process, but with the previous year.
I mean, I would like to find the last data for the month of last year recorded. For example:
This chart is leaked in the year 2022 (previous year) and I would like its latest data to be reflected in a chart for 2023. For example, in PLATINUM, 6,100 appears; that AZU shows 3,500. These are his latest records.
Could this be achieved?
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.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
97 | |
95 | |
77 | |
72 | |
65 |
User | Count |
---|---|
136 | |
109 | |
104 | |
82 | |
73 |