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 need to make a running total of a measure, I tried using a calculated table however that didnt work either and still gave me the same restult, I am assuming that I need to make some running totals somewhere inside my referenced formaules however not sure
Any help will be greatly appreciated
Measure is as below
Forecast Forward Volume Trend - Running = VAR MONTH1 = DIVIDE([MTD Volume (incl WIP) (Month -1) - PRIORDAY],[MTD Volume (incl WIP) (Month -1) - All],blank()) VAR MONTH2 = DIVIDE([MTD Volume (incl WIP) (Month -2) - PRIORDAY],[MTD Volume (incl WIP) (Month -2) - All],blank()) VAR MONTH3 = DIVIDE([MTD Volume (incl WIP) (Month -3) - PRIORDAY],[MTD Volume (incl WIP) (Month -3) - All],blank()) VAR YEAR1 = DIVIDE([MTD Volume (incl WIP) (YEAR -1) - PRIORDAY],[MTD Volume (incl WIP) (Year -1) - All],blank()) VAR Total = MONTH1+MONTH2+MONTH3+YEAR1 VAR Averagepercentpriorday =AVERAGEX(values(DIM_DATE[Date]),DIVIDE(total,4,blank())) VAR MONTH1b = DIVIDE([MTD Volume (incl WIP) (Month -1)],[MTD Volume (incl WIP) (Month -1) - All],blank()) VAR MONTH2b = DIVIDE([MTD Volume (incl WIP) (Month -2)],[MTD Volume (incl WIP) (Month -2) - All],blank()) VAR MONTH3b = DIVIDE([MTD Volume (incl WIP) (Month -3)],[MTD Volume (incl WIP) (Month -3) - All],blank()) VAR YEAR1b = DIVIDE([MTD Volume (incl WIP) (Year -1)],[MTD Volume (incl WIP) (Year -1) - All],blank()) VAR Totalb = MONTH1b+MONTH2b+MONTH3b+YEAR1b VAR Averagepercentday =AVERAGEX(values(DIM_DATE[Date]),DIVIDE(Totalb,4,blank())) VAR Percentdiff = Averagepercentday-Averagepercentpriorday VAR Forecastvalue = LOOKUPVALUE(TempTableCurrentMonth[Forecast Value],[LastDate],1) VAR FORECASTTREND = Percentdiff*Forecastvalue RETURN SUMX(CALCULATETABLE(FILTER(ALLSELECTED(DIM_DATE[Date]),DIM_DATE[Date]<=MAX(DIM_DATE[Date])),all(DIM_DATE[StartOfMonth])),FORECASTTREND)
Can you check with Quick measure option?
https://powerbi.microsoft.com/en-us/blog/quick-measures-preview
Thanks
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 |