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 ChampionCALCULATE(SUM(LD_PBI[VALUE % SHARE]),DATESINPERIOD(LD_PBI[Date],LASTDATE(LD_PBI[Date]),-12,MONTH))/SUMX (LD_PBI,[VALUE % SHARE]>0,1)
This just replaces your 12 with my formula.
You could test the SUMX (LD_PBI,[VALUE % SHARE]>0,1) as a measure and place it in a card in Power BI.
Signing off, but will check in tomorrow.
It also looks like your original formula if you replace the 12 (both times) with 3 or 6 will give you the average over the last 3 or 6 mo.
If this solves your issues, please mark it as the solution, so that others can find it easily. Kudos are nice too.
Nathaniel
hmm.. there's a problem with the measure ): yes, let's connect again tmr :)
- Nathaniel_C
I see...the If dropped off with all the copy and paste. Try this:
CALCULATE(SUM(LD_PBI[VALUE % SHARE]),DATESINPERIOD(LD_PBI[Date],LASTDATE(LD_PBI[Date]),-12,MONTH))/SUMX (LD_PBI, IF(LD_PBI[[VALUE % SHARE]]>0,1)
The blue is your original (check it for typos.) The red - after the "/" is the new portion and replaces the "12"
If this solves your issues, please mark it as the solution, so that others can find it easily. Kudos are nice too.
Nathaniel didnt work ): .. i think there's something wrong here - SUMX (LD_PBI, IF(LD_PBI[[VALUE % SHARE]]>0,1)
I tried using filter instead of if but the formula calculated for all months instead of within the specified period (12 months)
- Nathaniel_C
Hi joyceleeyw
SUMX (LD_PBI, IF(LD_PBI[Value % Share]>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 Can you include more period? i.e. from jan 17 to jan 19. the denominator has to still be 9 for a P12M average
- Nathaniel_C
Please be more explicit. What are you trying to do?
- Nathaniel_C
Based on your original question, did my solution work for you?
If this solves your issues, please mark it as the solution, so that others can find it easily. Kudos are nice too.
Nathaniel I need the denominator to only consider the P12M period when doing the SUMX
i thought of a measure like this. however, i think there's an error... are you able to help resolve the problem with my formula?
Share P12M = CALCULATE(SUM(LD_PBI[VALUE % SHARE]),DATESINPERIOD(LD_PBI[Date],LASTDATE(LD_PBI[Date]),-12,MONTH))/SUMX (LD_PBI, IF(DATESINPERIOD(LD_PBI[Date],LASTDATE(LD_PBI[Date]),-12,MONTH) && LD_PBI[Value % Share]>0,1))- Nathaniel_C
As this is really a new issue, you would do better by posting it on the forum. Good Luck!
If this solves your issues, please mark it as the solution, so that others can find it easily. Kudos are nice too.
Nathaniel