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

Help needed changing a calculated column into a measure.

Hello! 

 

I am VERY new to Power BI and have been combing through tons of pages and videos to teach myself. I am building a series of reports for my work place to monitor team member metrics, and I am stuck pretty good right now. I need to calculate the period number based on a ticket date submissions. I was able to find this great code for a calculated column (Code at the bottom changed to my table column names) and it works great! Problem is, I need to use the Period number to help slice the data by period. And calculated columns and slicers don't play together. We've got a few visuals on the page that reference the YTD average for the metric as well so adding the period filter to the whole page won't give all the data I need to display. The fix is to turn the calculated column into a measure, and I am completely lost on that one. 

 

CODE:  Period Number = CALCULATE(VALUES('Current Fiscal Year'[Period Number]), FILTER('Current Fiscal Year', 'Current Fiscal Year'[Period Start Date]<= EARLIER('5PT Grading Data'[Ticket Submission Date]) && 'Current Fiscal Year'[Period End Date]>= EARLIER('5PT Grading Data'[Ticket Submission Date])))

 

I've been doing reading and EARLIER doesn't seem to work well with Measures based on what I've found. I've done some reading on the DATESBETWEEN function but don't know how to apply that successfully to this if it's the correct funtion to use. 

 

I'd really like to avoid writing a long if/then statement that we'd need to update annually with the start and end date of each period. I'm trying to have as few edit touch points as possible, and future proof the process if someone new takes it over. If that's my only option, then that's my only option. But I wanted to ask. 
 
Thanks so much for any help you can offer! 

 

 
 
 
 
 
 
1 ACCEPTED SOLUTION
Douttful
Frequent Visitor

I was pulling the period data from the wrong tables. Once I corrected the data flow. That fixed everything. Thank you everyone for the help!!! 

View solution in original post

10 REPLIES 10
Douttful
Frequent Visitor

I was pulling the period data from the wrong tables. Once I corrected the data flow. That fixed everything. Thank you everyone for the help!!! 

View solution in original post

TheoC
Memorable Member
Memorable Member

Hi @Douttful 

 

Okay, my thoughts are as follows:

 

1. Create a Calculated Column that returns the Period Number into your Fact table such as below.

 

PeriodNumber = 

CALCULATE (
VALUES ( tblPeriod[Period Number ] ) ,
FILTER ( 'tblPeriod','tblPeriod'[Period Start Date] <= EARLIER ( tblFact[Submit Date] ) && 'tblPeriod'[ Period End date] >= ('tblFact'[Submit Date] ) )
)

2. Use the new Calculated Column "Period Number" as the basis of your measures and you should be able to get something like this (hoping this is what you want??):

 

TheoC_0-1634598124701.png

If the above is correct, then use the following measures:

SumbyCat1 = 

VAR _SumRes = SUM ( tblFact[L1 Resolved] )
VAR _SumByCat = CALCULATE ( _SumRes , FILTER ( ALL ( tblFact ) , 'tblFact'[L1 Resolved] = 1 ) )

RETURN 

_SumByCat
CountRows = 

VAR _PerfByAnalyst = COUNTROWS ( 'tblFact' ) 

RETURN 

_PerfByAnalyst
PercMatched = 

VAR _PercMatchbyCat = DIVIDE ( [SumbyCat1] , [CountRows] , 0 )

RETURN 

_PercMatchbyCat

The most important part is ensuring the Period Number is brought across into your Fact Table (i.e. tblFact).  From there, you can drag everything across 

 

Hope this is what you wanted? 🙂

If I have posted a response that resolves your question, please accept it as a solution to formally close the post.

Also, if you are as passionate about Power BI, DAX and data as I am, please feel free to reach out if you have any questions, queries, or if you simply want to connect and talk to another data geek!

Want to connect?  www.linkedin.com/in/theoconias

TheoC
Memorable Member
Memorable Member

Hi @Douttful,

 

Yes, you are spot on. Measure's do not like the EARLIER function (i.e. can't be used).  However, a work around is using MAX inside of a FILTER to achieve the same outcome.

 

Let me know if you would like me to define your measure based on your code or if you want to give it a go first.

 

A helpful resource to guide you through this: https://stackoverflow.com/questions/54893118/get-earlier-value-using-dax-measure

 

Hope this helps 🙂

 

PS - are you running a Calendar / Date table?  If you have established a Period Number (I am assuming something of the nature "202101" would represent "January 2021" or the Period Number 7 would represent either July or January dependent upon the Calendar / Fiscal Year requirement), you should be able to use the Slicer visual very simply to achieve your needs. Let me know if there is something on this topic that the Community and I can help with 🙂

If I have posted a response that resolves your question, please accept it as a solution to formally close the post.

Also, if you are as passionate about Power BI, DAX and data as I am, please feel free to reach out if you have any questions, queries, or if you simply want to connect and talk to another data geek!

Want to connect?  www.linkedin.com/in/theoconias

TheoC
Memorable Member
Memorable Member

@Douttful did you use the Calendar / Date table column in the slicer to achieve your outcome?

If I have posted a response that resolves your question, please accept it as a solution to formally close the post.

Also, if you are as passionate about Power BI, DAX and data as I am, please feel free to reach out if you have any questions, queries, or if you simply want to connect and talk to another data geek!

Want to connect?  www.linkedin.com/in/theoconias

Douttful
Frequent Visitor

Yes, I do have a calandar date table that has the start and end date for each fiscal period. we run on a 13 period fiscal year. Current Fiscal Year'[Period Number] references that table/column. 

 

I originally had the period numbers calculated in excel with the long if statement with the specific dates for the start and end of each period that kept erroring after a new line was added/extended. With that set up, the slicer worked great and I was super pumped (I learned about calculated columns after this and did not realize that the slicer didn't work with them until after.) 

 

I'm not 100% sure on how to apply the measure so that it filters the data the same way.

 

Background: I'm part of a call center and the metrics are about our analyst preformance. This one specifically is how many calls or issues the analyst is able to resolve per period and per year vs the total issues they report. 

 

This is my first post, so I'm also not sure on what sample data would be helpful. 

 

This is the layout for my date table

 

Current Fiscal YearPeriod Number Period Start Date Period End date
2021011/1/212/2/21
2021022/3/213/3/21
2021033/4/215/4/21

 

This is the layout for my metric data table 

 

Submitter IDReported SorceSubmit DateAssigneeSubmitter full name

L1 Resolved

111111Phone1/2/2021Vendor AssignedJack S.

0

222222Phone3/2/2021Freddy K. Freddy K. 

1

333333

Phone2/12/2021

Freddy K. 

Jason V. 

0

333333Phone4/2/2021Jason V. Jason V. 

1

 

The L1 Resolved column is a calculated column that matches the submitter fill name to the assignee feild. If it's true, it enters a 1, if it isn't it enters a 0. Allowing to sum and average the data. (This is how the data comes out of our reports and can't be made better sadly) 

 

That column is what I use to get the actual metric I need, but I'm not able to filter the sum via any of the slicers, I thought it was because the period column I was trying to use was a calculated column. Could it also not be working because the L1 Resolved column is calculated too? 

 

TheoC
Memorable Member
Memorable Member

Hi @Douttful 

 

In terms of the Calculated Column you've put together, the logic makes sense in terms of returning a 1 for a match and 0 where there is no match. However, if you are using a SUM on a column, there is no real way to slice using a SUM value given that it would be a total of all the 1s in the column which effectively gives you a single total figure.

 

If you are wanting to filter based on a match (i.e. the '1s'), then just use the Slicer visual and drag the existing L1 Resolved column as is, without any count / sum, and you will be able to filter by 1 or 0 accordingly.

 

With respect to SUM / AVERAGE, etc., what exactly are you aiming to SUM or AVERAGE?  Is it the frequency that an Assignee and a Submitter match?  For example, are you wanting to SUM the frequency that 1 occurs at the analyst name level (e.g. Assignee = Freddy K has matched Submitter = Freddy K in 10 instances during Period 1 or 2021)?

 

Thanks heaps,
Theo 🙂

If I have posted a response that resolves your question, please accept it as a solution to formally close the post.

Also, if you are as passionate about Power BI, DAX and data as I am, please feel free to reach out if you have any questions, queries, or if you simply want to connect and talk to another data geek!

Want to connect?  www.linkedin.com/in/theoconias

Douttful
Frequent Visitor

Number of tickets that were resolved, and the percentage vs total ticket submission. And the average of both metrics per period and per year. 

 

The column is working so far, and I've got the counts dragged out as well. It's the filtering per period/working with the slicer that I'm stuck on. 

 

Based on the article you shared I THINK below is the right code for the measure to calculate period number via submit date, but I'm not sure how to apply that measure to the data in order to be able to filter per period on the visuals of my report. 

 

CALCULATE(VALUES('Current Fiscal Year'[Period Number]), FILTER('Current Fiscal Year''Current Fiscal Year'[Period Start Date]<= MAX('L1 TK Resolve Data'[Submit Date]) && 'Current Fiscal Year'[Period End Date]>= MAX('L1 TK Resolve Data'[Submit Date]))) 
TheoC
Memorable Member
Memorable Member

Hi @Douttful 

 

Okay, my thoughts are as follows:

 

1. Create a Calculated Column that returns the Period Number into your Fact table such as below.

 

PeriodNumber = 

CALCULATE (
VALUES ( tblPeriod[Period Number ] ) ,
FILTER ( 'tblPeriod','tblPeriod'[Period Start Date] <= EARLIER ( tblFact[Submit Date] ) && 'tblPeriod'[ Period End date] >= ('tblFact'[Submit Date] ) )
)

2. Use the new Calculated Column "Period Number" as the basis of your measures and you should be able to get something like this (hoping this is what you want??):

 

TheoC_1-1634598162192.png

 


If the above is correct, then use the following measures:

 

 

 

SumbyCat1 = 

VAR _SumRes = SUM ( tblFact[L1 Resolved] )
VAR _SumByCat = CALCULATE ( _SumRes , FILTER ( ALL ( tblFact ) , 'tblFact'[L1 Resolved] = 1 ) )

RETURN 

_SumByCat

 

 

 

 

 

CountRows = 

VAR _PerfByAnalyst = COUNTROWS ( 'tblFact' ) 

RETURN 

_PerfByAnalyst

 

 

 

 

 

PercMatched = 

VAR _PercMatchbyCat = DIVIDE ( [SumbyCat1] , [CountRows] , 0 )

RETURN 

_PercMatchbyCat

 

 

 

 

The most important part is ensuring the Period Number is brought across into your Fact Table (i.e. tblFact).  From there, you can drag everything across 🙂

 

Hope this is what you wanted?

If I have posted a response that resolves your question, please accept it as a solution to formally close the post.

Also, if you are as passionate about Power BI, DAX and data as I am, please feel free to reach out if you have any questions, queries, or if you simply want to connect and talk to another data geek!

Want to connect?  www.linkedin.com/in/theoconias

Douttful
Frequent Visitor

CALCULATE(VALUES('Current Fiscal Year'[Period Number]), FILTER('Current Fiscal Year', 'Current Fiscal Year'[Period Start Date]<= MAX('L1 TK Resolve Data'[Submit Date]) && 'Current Fiscal Year'[Period End Date]>= MAX('L1 TK Resolve Data'[Submit Date]))) 
 
Is this right?
TheoC
Memorable Member
Memorable Member

@Douttful, I don't have visibility over your data, format, etc, so without access to sample data I cannot tell you it will or will not work. But I do recommend attempting to apply what the Community provides in terms of recommendations/advice. It would be good to find out if your application of the measure works or does not after attempting it.

If I have posted a response that resolves your question, please accept it as a solution to formally close the post.

Also, if you are as passionate about Power BI, DAX and data as I am, please feel free to reach out if you have any questions, queries, or if you simply want to connect and talk to another data geek!

Want to connect?  www.linkedin.com/in/theoconias

Helpful resources

Announcements
UG GA Amplification 768x460.png

Launching new user group features

Learn how to create your own user groups today!

November Power BI Update 768x460.png

Check it Out!

Click here to read more about the November 2021 Updates!

M365 768x460.jpg

Microsoft 365 Collaboration Conference | December 7–9, 2021

Join us, in-person, December 7–9 in Las Vegas, for the largest gathering of the Microsoft community in the world.