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
Saw_Dusted
New Member

Compare previous month data to current month

I have a dashboard to display monthly install base totals (for equipment).  The data for the dash is comprised of a previous month dataset and current month dataset in the same sheet, identified by a number representing the month (2 - Feb, 3 Mar, etc)  What i was asked do was create a visualization to show if the current month total is above or below last month.  Each piece of equipment has a unique serial number, so that is what i am counting to get my totals.  

Is there a visualization that will allow me to use my previous month total as a goal and put the current month count against it?

 

I tried using the Gauge and KPI but was not successful.  i would like to let the system change the totals by adjusting filters if possible instead of going into the visualization to manipulate goal number (if possible).  I will likely have several of these visualizations displaying regional numbers, so making one change vs several is more efficient.

 

If this explination is not clear please let me know. Thanks

 

6 REPLIES 6
dedelman_clng
Community Champion
Community Champion

Can you share the measures you created that don't work in the gauge visualization?  You should be able to put slice-able measures in all 4 spots (min, max, target, value) on that visualization, so it may be a fault in your measures.

I expect it is the way i am tring to use the visualization.  I do not have a slicer on this particular page, I am creating/presenting this particular data for a group that wants a "one page" view showing if we are up for the month or down.  I am able to get the gauge to show the current month data correctly, where i am hitting a wall is getting the target value to appear (last month total instrument count).  In the picture I have "month" in the target value spot, but that will not work as I have no way to filter that specific value to count "2" or the previous month.  

 

IB.png

You will want to write a measure that gets the value you are showing (SERIAL NUMBER) but for the previous month.

 

Something like this:

 

PrevMonth = CALCULATE( COUNTA( Table[Serial Number] ), PARALLELPERIOD( Table[Date], -1, MONTH) )

I tried this measure but receive an error when i put it into the target goal position.  I changed the Table assignments to match my data (Table1[SERIAL_NUMBER]) for instance.  I get the following error:

 

PBI KPI ERR.png

 

The Month value that i am working with is just a number representation (1 for January, 2 for February, etc) that I manually add to my spreadsheet.  There is no unique key in my data that can identify when i exported it, that is why i manuall fill in.  Should this be a full date format?

Thank you for your assistance.

Hi @Saw_Dusted

Have you resolved your issue? If you have, please mark corresponding reply as answer, and welcome to share your own solution. more people will benefit from it.


Best Regards,
Angelia

Yes you should be using a Date type field to use PARALLELPERIOD.  You could continue to use integers, but then you will want to add a year column in addition to the month column to account for what you want to show when it is January.  That way is more complicated (lots of IF), so making the Month value be a Date that is the first day of the month is going to be your easiest path.

 

Hope this helps

David

 

 

 

 

 

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.