Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
I have searched everywhere on this forum and others to look for an answer but I have not been able to figure it out. I want to have a measure that calculates the total average of the last 3 months not including this month. And another measure that calculates the average of last three months on this date. So I can use a card measure to calc = last3MonthAvg / TotalLast3monthAvg and get a %.
Eg. in excel it is very simple:
But I need the '%PerDay' as a measure so I can multiply it by CurrentDates sales. I have managed to do the below in pivot table but have not been able to hold a measure with a single value such as the Avg. All help is appreciated!
Solved! Go to Solution.
You may take a look at the following posts.
Use the following formula for 3 Month, use BLANK () so that I would not get sales of the current month blank
Where Mes1 = CALCULATE ([Net Sales ($)]; PARALLEL PERIOD (Calendar[Date];-1; My ))
Where Mes2 = CALCULATE ([Net Sales ($)]; PARALLEL PERIOD (Calendar[Date];-2; My ))
Where Mes3 = CALCULATE ([Net Sales ($)]; PARALLEL PERIOD (Calendar[Date];-3; My ))
Where Mes4 = CALCULATE ([Net Sales ($)]; PARALLEL PERIOD (Calendar[Date];-4; My ))
Where Mes5 = CALCULATE ([Net Sales ($)]; PARALLEL PERIOD (Calendar[Date];-5; My ))
Where Mes6 = CALCULATE ([Net Sales ($)]; PARALLEL PERIOD (Calendar[Date];-6; My ))
Where M1 = Yes ( Mes1 <=0;0;1)
Where M2 = Yes ( Mes2 <=0;0;1)
Where M3 = Yes ( Mes3 <=0;0;1)
Where M4 = Yes ( Mes4 <=0;0;1)
Where M5 = Yes ( Mes5 <=0;0;1)
Where M6 = Yes ( Mes6 <=0;0;1)
return
Yes ([Net Sales ($)]= BLANK (); BLANK (); DIVIDE ( Mes1 + Mes2 + Mes3 + Mes4 + Mes5 + Mes6 ; M1 + M2 + M3 + M4 + M5 + M6 ))
You may take a look at the following posts.
@israabuhasna You can use a rolling formula with a date dimension .
Example
Rolling 3 = CALCULATE(Average(Sales[Sales Amount]),DATESINPERIOD('Date'[Date Filer],MAX(Sales[Sales Date]),-3,Day))
You can use DATESINPERIOD function, please see the below.
https://docs.microsoft.com/en-us/dax/datesinperiod-function-dax
Thank you for responding. It did not work. It will not give me a single value.
User | Count |
---|---|
141 | |
113 | |
104 | |
78 | |
64 |
User | Count |
---|---|
136 | |
125 | |
107 | |
70 | |
61 |