cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
pbideveloper
Frequent Visitor

Need suggestion on creating a measure

Hi All,

 

I'm completely new here in power BI would  like some advice for the requirement I have to achieve the requirement which goes like this I have to showcase the data for the last month when a quarter is selected. example, when the quarter 1 is selected I have to showcase the data of March as March is the last month of the quarter,1 is My data doesn't have a date field column to use it. My data comes in columns like 1,2,3 up to 12 and year as well.

 

Can any well please guide me in achieving this. thanks in advance

2 ACCEPTED SOLUTIONS
Cmcmahan
Resident Rockstar
Resident Rockstar

If you data doesn't have a date field  column, then how are you specifying which quarter the data belongs to?

 

If you just have MonthNum (Jan =1, Feb=2, etc) you can still use that as date information. How are you having the user select a quarter?  There's a really easy mathematical formula you can use to get the month number of the last month in a given quarter, but first you have to have a way to get that given quarter:

LastMonthofQuarter = [Quarter Value]*3

And then you would filter any data you get like this:

ShowcaseData = CALCULATE( AVERAGE(Data[Amount]), //Whatever sort of math or values you want to show off
Data[Month]=[LastMonthofQuarter] &&
Data[Year]=MAX(Data[Year]) //Makes sure we're only getting data from one year
)

View solution in original post

v-piga-msft
Resident Rockstar
Resident Rockstar

Hi @pbideveloper ,

Have you solved your problem?

If you have solved, please always accept the replies making sense as solution to your question so that people who may have the same question can get the solution directly.

If you still need help, please share your data sample as table format and your desired output so that we could help further on it.

Best  Regards,

Cherry

 

Community Support Team _ Cherry Gao
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

7 REPLIES 7
v-piga-msft
Resident Rockstar
Resident Rockstar

Hi @pbideveloper ,

Have you solved your problem?

If you have solved, please always accept the replies making sense as solution to your question so that people who may have the same question can get the solution directly.

If you still need help, please share your data sample as table format and your desired output so that we could help further on it.

Best  Regards,

Cherry

 

Community Support Team _ Cherry Gao
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

Cmcmahan
Resident Rockstar
Resident Rockstar

If you data doesn't have a date field  column, then how are you specifying which quarter the data belongs to?

 

If you just have MonthNum (Jan =1, Feb=2, etc) you can still use that as date information. How are you having the user select a quarter?  There's a really easy mathematical formula you can use to get the month number of the last month in a given quarter, but first you have to have a way to get that given quarter:

LastMonthofQuarter = [Quarter Value]*3

And then you would filter any data you get like this:

ShowcaseData = CALCULATE( AVERAGE(Data[Amount]), //Whatever sort of math or values you want to show off
Data[Month]=[LastMonthofQuarter] &&
Data[Year]=MAX(Data[Year]) //Makes sure we're only getting data from one year
)

View solution in original post

Hi @Cmcmahan,

I'm really sorry for the the late reply,

Thanks for the solution suggested it worked for what i wanted to achieve, but unfortunately i landed to some other problem .

 

 Now i have two measures:

1. Closing Headcount: this will give me headcount count of each month when user selects month.

2. LastQuarterValue: This measures calculates the last month headcount value of the selected quarter.

 

The problem here is now when user selects quarter slicer  i have to show the measure 2 value whch i have created based on your suggestion. if user selects month slicer i have to show that particular month value.

 

The other problem i would say if the current month is 7 (july ) which is the start of quarter 3. the measure which u gave is giving me blank. rather it has to give me july month headcount data . If possible can you please suggest anything on that.

 

Below is the screenshot:

Headcount.png

Hi,

Share the link from where i can download your PBI file.  Please show the expected result there.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

Hi @Ashish_Mathur ,

 

I have explained few scenarios which might help you in unsrstanding the issue i'm facing.

Attaching screen shots for reference ,

Scenario 1: When the user selects month slicer it should show him that month headcount data.

Below is the sample headcount data for January.

scenario1.png

 

Scenario 2: When the user selects quarter slicer it should show him last month of the selected quarter headcount data.scenario2.png

 

 

Scenario 3: Suppose when user selects quarter 1 and I do not have data for last month (March) of quarter 1. Example current month is feb and I do not have data for march month then I would like to show him maximum month which is present in the quarter that will be feb month headcount data.Here if user selects quarter 1 the end user should see data like this. Where below is sample headcount of March.

 

 

My Pbix is live connected to analysis server. I might not be able to share the link as it is having some client reated data. is there any other way that i can share this.

 

 

Hi,

Anonymise the dataset and share the link from where i can download the PBI file.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/
Joorge_C
Resolver II
Resolver II

You can try to link in additional Data (like from Excel), where you map the Month in number to your Quarters.

 

So for example if you have a simple 2 column table in Excel with your input and output desired, you can link this to your data and then use this as the filter.

Helpful resources

Announcements
2022 Release Wave 1 760x460.png

2022 Release Wave 1 Plan

Power Platform release plan for the 2022 release wave 1 describes all new features releasing from April 2022 through September 2022.

Power BI December 2021 Update_carousel 768x460.jpg

Check it Out!

Click here to read more about the December 2021 Updates!

User Group Leader Meeting January 768x460.png

Calling all User Group Leaders!

Don't miss the User Group Leader meetings on January, 24th & 25th, 2022.

Jan 2022 Dev Camp 768x460 copy.png

Power BI Dev Camp- January 27th, 2022

Mark your calendars and join us for our next Power BI Dev Camp!

Top Solution Authors
Top Kudoed Authors