Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hi,
I have power bi report and would like to create a DAX measure to calculate the SLA % of the total but for the last month dynamically.
Here is a sample of my data:
Month Year Within SLA Total % within SLA
Sep-21 10 50 20
Oct-21 5 50 10
Nov-21 7 100 7
I would like to create a measure to work out the Nov21 % SLA
but I would like the calculation to be dynamic so that is uses the last month measure.
So when we run the report is run again in say January it uses Dec21 for calculating the %SLA.
Thanks
Solved! Go to Solution.
Hi @Abs_N ,
According to your description, I think you are going to calculate the ratio value for the previous month.
You can try this measure:
% previous month = var _start=EOMONTH(TODAY(),-2)
var _end=EOMONTH(TODAY(),-1)
return DIVIDE(CALCULATE(SUM('Table'[Within]),FILTER('Table',[Month Year]<=_end&&[Month Year]>_start)),CALCULATE(SUM('Table'[SLA Total]),FILTER('Table',[Month Year]<=_end&&[Month Year]>_start)))
EOMONTH function returns the date in datetime format of the last day of the month, before or after a specified number of months.
Here's the result.
Best Regards,
Stephen Tao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi @Abs_N ,
According to your description, I think you are going to calculate the ratio value for the previous month.
You can try this measure:
% previous month = var _start=EOMONTH(TODAY(),-2)
var _end=EOMONTH(TODAY(),-1)
return DIVIDE(CALCULATE(SUM('Table'[Within]),FILTER('Table',[Month Year]<=_end&&[Month Year]>_start)),CALCULATE(SUM('Table'[SLA Total]),FILTER('Table',[Month Year]<=_end&&[Month Year]>_start)))
EOMONTH function returns the date in datetime format of the last day of the month, before or after a specified number of months.
Here's the result.
Best Regards,
Stephen Tao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi @Greg_Deckler ,
no the percentage is 7% however I want the calculation to be dynamic in that it always uses the month end measure
so say we have a card for this month end measure and it will return 7%
then when we run the report again after month and the dataset has Decemeber data the card will automatically pick up Decemebers data and show the new %.
I want the measure to the automatically use the latest month data.
@Abs_N Oh, then you just need Lookup Min/Max where you find your max date value and then grab the items from that row, like below. Best if you have an actual date column or index column to find the "max".
Measure =
VAR __Last = MAX('Table'[Month Year])
VAR __Within = MAXX(FILTER('Table',[Month Year]=__Last),[Within])
VAR __SLATotal = MAXX(FILTER('Table',[Month Year]=__Last),[SLA Total])
RETURN
__Within / __SLATotal * 100
Hi @Greg_Deckler
I think we are nearly there . . .
The underlying data is as follows
Month Year | Within | Total |
Sep-21 | 0 | 1 |
Oct-21 | 1 | 1 |
Nov-21 | 1 | 1 |
The original table is a summary
The "Within" is a sum and the total is a count of all the items.
I have tried checking the within variable but I think my syntax is incorrect.
Here is what I have at the moment . .
@Abs_N So are you saying that for November that the % within SLA should be 10%, not 7%?
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
109 | |
96 | |
77 | |
66 | |
53 |
User | Count |
---|---|
144 | |
105 | |
102 | |
89 | |
63 |