Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
rkcasey
Frequent Visitor

Gauge with FY to date total compared to last year Total FY

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? 

3 REPLIES 3
v-shex-msft
Community Support
Community Support

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

Community Support Team _ Xiaoxin
If this post helps, please consider accept as solution to help other members find it more quickly.

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.

5-11-2017 10-48-44 AM.jpg 

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

Community Support Team _ Xiaoxin
If this post helps, please consider accept as solution to help other members find it more quickly.

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.