Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
So I would like to set up a gauge where I show the current fiscal year (FY) to date compared to last year FY to date and set the max number for the gauge at the total for last FY. I have a table with the fiscal year info and build a hierarchy for it and it is working as expected. I then wrote a measure FY17total$ = CALCULATE(sum('Task data'[$ worked]),FILTER('Task data',RELATED('Fiscal Year'[Fiscal Year])="FY17")) - this gives the total and gives the by month for last fiscal year but I can't figure out how to show this compared to the current data to date for THIS Fiscal year AND show it on a guage. Seem like an obvious usage but I am struggling. Suggestions?
Hi @rkcasey,
I'd like to suggest you use "lastdate" function to get the last date from "Task data" table, then calculate to get the related fiscal year.
For example:
Last Fiscal Year=calculate(max(Fiscal Year[Fiscal Year]) ,LASTDATE(Task data[Date]))
If above not help, please share some sample data to test..
Regards,
Xiaoxin Sheng
Xiaoxin
So that was helpful in pointing me in right direction but I am still not quite there. So I get that I can't filter the visual in a way that will impact the max, min and target. So I was able to write a measure for FY17 and FY18 actuals and have set FY17 as the max and FY18 as the value. This gets me gauge that looks pretty good but I am still not able to write some thing for the target value.
I used your suggestion for last date and it works perfectly to pull out the correct last month that there is data (currently April). So I would assume I need to use something like measure name = CALCULATE(sum('Task data'[Hours Worked]),FILTER('Task data',RELATED('Fiscal Year'[Fiscal Year])="FY17")) but would also need to add another filter for all the values for the months between the start of the FY and the month from the last date calculation? any other thoughts would be appreciated.
Hi @rkcasey,
Yes, gauge visual only show the summary value, you need to add a filter or slicer to operation the filtered date range.
Regards,
Xiaoxin Sheng
User | Count |
---|---|
140 | |
113 | |
104 | |
77 | |
64 |
User | Count |
---|---|
135 | |
118 | |
101 | |
71 | |
61 |