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.
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.
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 |
---|---|
113 | |
100 | |
78 | |
76 | |
52 |
User | Count |
---|---|
146 | |
109 | |
106 | |
88 | |
61 |