Need suggestion on creating a measure

[pbideveloper]

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

[Cmcmahan · Accepted solution]

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
                         )

[v-piga-msft · Accepted solution]

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

[Cmcmahan · Accepted solution]

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
                         )

[pbideveloper]

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:

[Ashish_Mathur]

Hi,

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

[pbideveloper]

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.

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

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.

[Ashish_Mathur]

Hi,

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

[Joorge_C]

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.