Forum Discussion
Calculating P12M data but excluding blank months
Hi joyceleeyw ,
One sample for your reference, please check the following steps as below.
1. Create a calculated column in the fact table.
YM = FORMAT('Table'[date],"yyyymmmm")2. After that, we can create measures as below to get P12 or P6 average.
average p12M = VAR A = MAX ( 'Table'[date] ) VAR p12 = EDATE ( A, -12 ) RETURN DIVIDE ( CALCULATE ( SUM ( 'Table'[value] ), FILTER ( 'Table', 'Table'[date] >= p12 && 'Table'[date] <= A ) ), CALCULATE ( DISTINCTCOUNT ( 'Table'[YM] ), FILTER ( 'Table', 'Table'[date] >= p12 && 'Table'[date] <= A ) ) )average p6M = VAR A = MAX ( 'Table'[date] ) VAR p12 = EDATE ( A, -6 ) RETURN DIVIDE ( CALCULATE ( SUM ( 'Table'[value] ), FILTER ( 'Table', 'Table'[date] >= p12 && 'Table'[date] <= A ) ), CALCULATE ( DISTINCTCOUNT ( 'Table'[YM] ), FILTER ( 'Table', 'Table'[date] >= p12 && 'Table'[date] <= A ) ) )For more details, please check the pbix as attached.
Community ChampionHi joyceleeyw ,
Try Average = DIVIDE ([Total Sales Revenue], SUMX (Table, IF(Table[SalesRevenue]>0,1))
If this solves your issues, please mark it as the solution, so that others can find it easily. Kudos are nice too.
Nathaniel
Hi Nathaniel,
Sorry i wasnt clear in my question
The current formula i am using to calculate my P12M average is
CALCULATE(SUM(LD_PBI[VALUE % SHARE]),DATESINPERIOD(LD_PBI[Date],LASTDATE(LD_PBI[Date]),-12,MONTH))/12
However, i need a measure that when jan-mar is empty, to automatically calculate 9 months average (sum all and divide by 9) instead of 12 which what my formula is currently doing
- Nathaniel_C
Hi joyceleeyw ,
So no problem.
SUMX (Table, IF(Table[SalesRevenue]>0,1) replaces your 12. Table is the name of table in your case it seems to be LD_PBI
So SUMX (LD_PBI,[Whatever column that has your sales revenue]>0,1) just asks whether there is any revenue for that month, if there is, it gives you a 1. Then it sums those up, and works for your denominator. It can be any month missing or none it will always give you the proper value.
One other thing, you are using "/", instead you might use the DIVIDE () as it doesn't create a problem in divide by zero...also known as the "safe" divide. DIVIDE(numerator,denominator)
If this solves your issues, please mark it as the solution, so that others can find it easily. Kudos are nice too.
NathanielHi Nathaniel_C
MY data contains date from jul 2017 - jul 2019, more than 2 years. Which part of the formula tells power bi to do it for just 12 months and not the full 2 years?
Also cause cause i need to do a 3 months and 6 months average as well with the same data set- Nathaniel_C
Hi joyceleeyw ,
So it looks like DATESINPERIOD(LD_PBI[Date],LASTDATE(LD_PBI[Date]),-12, is giving you a running total over the last 12 months, is that correct?
If this solves your issues, please mark it as the solution, so that others can find it easily. Kudos are nice too.
Nathaniel
Please use [VALUE % SHARE]
I want to find the average value share over a period of 12 months
Table name - LD_PBI
Date column name - LD_PBI[Date]
Value share column name - LD_PBI[VALUE % SHARE]
CALCULATE(SUM(LD_PBI[VALUE % SHARE]),DATESINPERIOD(LD_PBI[Date],LASTDATE(LD_PBI[Date]),-12,MONTH))/12
- Nathaniel_C
Is [Value % Share] have a monthly amount for your table?