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

Calculate value for a specific month

Hi,

I'm pretty new to power bi and am trying to get a value for the baseline of a kpi. I have a measure for calcualting the average number of issues on a document. There are lots of documents each month. I can use a card and a month filter on the report to show the average number of issues in Jan 2020. I'd like to know how I can "record" or calculate this as a number that can be used in a kpi. For instance, my kpi is likely to be a 20% reduction in issues from Jan to Dec 2020. Thanks for any help you can provide.

1 ACCEPTED SOLUTION
v-zhenbw-msft
Community Support
Community Support

Hi @ithomas2 ,

 

We can use the following steps to meet your requirement.

 

1. We can create a month column.

 

Month = MONTH(‘Table’[Date])

 

2. Then we can create a two measures, one is the average of each month based on the month slicer, another is the average of end of year month.

And we can put them to a KPI card, the result like this,

 

select month AVG = 
CALCULATE(AVERAGE('Table'[value]))

 

End of year AVG = CALCULATE(AVERAGE('Table'[value]),'Table'[Month] = 12)

 

C 1.jpg

 

If it doesn’t meet your requirement, could you please show the exact expected result based on the table that we have shared?

 

BTW, pbix as attached.

 

Best regards,

 

Community Support Team _ zhenbw

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

2 REPLIES 2
v-zhenbw-msft
Community Support
Community Support

Hi @ithomas2 ,

 

We can use the following steps to meet your requirement.

 

1. We can create a month column.

 

Month = MONTH(‘Table’[Date])

 

2. Then we can create a two measures, one is the average of each month based on the month slicer, another is the average of end of year month.

And we can put them to a KPI card, the result like this,

 

select month AVG = 
CALCULATE(AVERAGE('Table'[value]))

 

End of year AVG = CALCULATE(AVERAGE('Table'[value]),'Table'[Month] = 12)

 

C 1.jpg

 

If it doesn’t meet your requirement, could you please show the exact expected result based on the table that we have shared?

 

BTW, pbix as attached.

 

Best regards,

 

Community Support Team _ zhenbw

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

parry2k
Super User
Super User

@ithomas2 you need to create a measure for Previous month which can be achieved by using Time Intelligence DAX functions. As a best practice, add date dimension in your model and use it for and time intelligence calculations. There are many posts on how to add date dimension and below is the link to a few. Once the date dimension is added, mark it as a date table on table tools.

https://www.sqlbi.com/articles/creating-a-simple-date-table-in-dax/
https://radacad.com/create-a-date-dimension-in-power-bi-in-4-steps-step-1-calendar-columns

 

Once you had date dimension in your model, use PREVIOUSMONTH DAX function and that you can use KPIs to calculate the % difference.

 

I would  Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos whoever helped to solve your problem. It is a token of appreciation!



Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!

Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo

If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤


Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.

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.