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 all,
I have the below measure which calculates the averge bill rate excluding State but including Division, Assignment Type, Speciality, Bill_Rate_Tier & Startdate
I have a filter for the Startdate where user can select a date.
When user selects a data from StartDate filter, I would like the below calculation to last 12 months worth of data and give the average bill rate.
Could you please help me how i can amend this measure to calculate average for last 12 months?
CALCULATE(AVERAGE(Query1[Bill Rate]),ALL(Query1[State]),ALLSELECTED(Query1[Division],Query1[Assignment Type],Query1[Speciality],Query1[BILL_RATE_TIER],Query1[STARTDATE]))
Solved! Go to Solution.
Hi @Anonymous ,
First, please create a Date dimension table and use Date field of Date table in slicer. Then create a measure as below:
Rolling 12 months average =
VAR _seldate =
SELECTEDVALUE ( Date[Date] )
VAR _startdate =
DATE ( YEAR ( _seldate ) - 1, MONTH ( _seldate ) - 1, 1 )
VAR _enddate =
EOMONTH (
DATE ( YEAR ( _seldate ), MONTH ( _seldate ) - 1, DAY ( _seldate ) ),
0
)
RETURN
CALCULATE (
AVERAGE ( Query1[Bill Rate] ),
DATESBETWEEN ( Query1[StartDate], _startdate, _enddate ),
ALL ( Query1 )
)
Best Regards
Rena
@Anonymous , Join start date with date from date table and try a formula like
Rolling 12 = CALCULATE(sum(Sales[Sales Amount]),DATESINPERIOD('Date'[Date],ENDOFMONTH(Sales[Sales Date]),-12,MONTH))
Rolling 12 = CALCULATE(sum(Sales[Sales Amount]),DATESINPERIOD('Date'[Date ],MAX(Sales[Sales Date]),-12,MONTH))
Rolling 12 = CALCULATE(sum(Sales[Sales Amount]),DATESINPERIOD('Date'[Date ],MAX('Date'[Date ]),-12,MONTH))
To get the best of the time intelligence function. Make sure you have a date calendar and it has been marked as the date in model view. Also, join it with the date column of your fact/s. Refer :
https://radacad.com/creating-calendar-table-in-power-bi-using-dax-functions
Appreciate your Kudos.
thanks Amit
The below is taking the dates from selected date till the last 12 months.
For example : if 8/28/2020 is selected in the filter, its calculating 12 months from this date.
Rolling 12 = CALCULATE(sum(Sales[Sales Amount]),DATESINPERIOD('Date'[Date],ENDOFMONTH(Sales[Sales Date]),-12,MONTH))
What i would like to do is, take the last day of the previous month and calculate 12 months from this date.
For example: if 8/28/2020 is selected in the filter, it should calculate from 7/1/2019 till 7/31/2019
Hi @Anonymous ,
First, please create a Date dimension table and use Date field of Date table in slicer. Then create a measure as below:
Rolling 12 months average =
VAR _seldate =
SELECTEDVALUE ( Date[Date] )
VAR _startdate =
DATE ( YEAR ( _seldate ) - 1, MONTH ( _seldate ) - 1, 1 )
VAR _enddate =
EOMONTH (
DATE ( YEAR ( _seldate ), MONTH ( _seldate ) - 1, DAY ( _seldate ) ),
0
)
RETURN
CALCULATE (
AVERAGE ( Query1[Bill Rate] ),
DATESBETWEEN ( Query1[StartDate], _startdate, _enddate ),
ALL ( Query1 )
)
Best Regards
Rena
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 |
---|---|
111 | |
95 | |
80 | |
68 | |
59 |
User | Count |
---|---|
150 | |
119 | |
104 | |
87 | |
67 |