Forum Discussion
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.
10 Replies
- TheoC
Community Champion
Hi Anonymous,
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 🙂
- TheoC
Anonymous, 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.
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 Year Period Number Period Start Date Period End date 2021 01 1/1/21 2/2/21 2021 02 2/3/21 3/3/21 2021 03 3/4/21 5/4/21 This is the layout for my metric data table
Submitter ID Reported Sorce Submit Date Assignee Submitter full name L1 Resolved
111111 Phone 1/2/2021 Vendor Assigned Jack S. 0
222222 Phone 3/2/2021 Freddy K. Freddy K. 1
333333
Phone 2/12/2021 Freddy K.
Jason V. 0
333333 Phone 4/2/2021 Jason 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
Hi Anonymous
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 🙂
- TheoC
Anonymous did you use the Calendar / Date table column in the slicer to achieve your outcome?
- TheoC
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??):
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? 🙂
