Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

Reply
Anonymous
Not applicable

Last 12 Months Graph not working for DIVIDE - Please Help

Im currently trying to create a CurrentMonthTotal and a Last12MonthsTotals Graph that look like the image below.:

 

RSebastiani_0-1631238673253.png

I have 4 values that I use to calculate this number:  

 

_M_LocationID = DISTINCTCOUNT(gmb_reviews[location_id])
_M_ReviewID = DISTINCTCOUNT(gmb_reviews[review_id])
_M_Avg#ReviewsByLocation = DIVIDE([_M_ReviewID],[_M_LocationID])
 
_M_Avg#ReviewsByLocation | Last 12 Months =
VAR MaxDate = MAX('DimDate'[FullDateAlternateKey])
VAR MaxDate_6MonthsAgo = EOMONTH(MaxDate, -13)

VAR Result =
IF(
HASONEVALUE('Presentation Calendar'[YYYYMM]) &&
MAX('Presentation Calendar'[FullDateAlternateKey]) <= MaxDate &&
MIN('Presentation Calendar'[FullDateAlternateKey]) > MaxDate_6MonthsAgo,
CALCULATE(
[_M_Avg#ReviewsByLocation],
FILTER(ALL(DimDate[Month],DimDate[Year],DimDate[YYYYMM]),
DimDate[Month] = VALUES('Presentation Calendar'[Month]) &&
DimDate[Year] = VALUES('Presentation Calendar'[Year]))
),
BLANK()
)
RETURN
Result

 

 

I am using the _M_Avg#ReviewsByLocation measure to calculate the CurrentMonthTotal and the _M_Avg#ReviewsByLocation | Last 12 Months for the last 12 months.  It's for some reason adding them all and showing me the same total across the board.  This is what the underlying data looks like.:

 

_M_Avg#ReviewsByLocation_M_LocationID_M_ReviewIDYearMonth
1112019January
1112019February
1112019March
1222020January
1222020September
1.29112020October
1.312162020November
1772020December
1552021January
1442021February
1.3682021March
1.3792021April
1.38102021May
1.210122021June
1.312162021July
1.412172021August
1222021September

I have 2 parameters for month and year which the CurrentMonthTotal should show for the month/year picked and the Last 12 Month total should show from that year/month back 12 months.  The numbers should be in the 1.2, 1.5, 1.7 per month, but as you can see its showing me the total for all.  I am not sure how to fix.  

 

This is a sample of the expected result (with the right numbers shown in the table above).  Notice every month has it's own different value and not the same for evey month as the current graph shows (very top)

 

RSebastiani_0-1631240618072.png

 

 

Any help is much appreciated.  Thank you!

3 REPLIES 3
Ashish_Mathur
Super User
Super User

Hi,

It is best to show some data (in a format that can be pasted in an MS Excel file) and the expected result in a Table format.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/
Anonymous
Not applicable

Here you go .. thank you.

 

_M_Avg#ReviewsByLocation_M_LocationID_M_ReviewIDYearMonth
1112019January
1112019February
1112019March
1222020January
1222020September
1.29112020October
1.312162020November
1772020December
1552021January
1442021February
1.3682021March
1.3792021April
1.38102021May
1.210122021June
1.312162021July
1.412172021August
1222021September

Hi,

Please also show the expected result (as requested in the previous message).


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

Helpful resources

Announcements
RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

MayPowerBICarousel

Power BI Monthly Update - May 2024

Check out the May 2024 Power BI update to learn about new features.

LearnSurvey

Fabric certifications survey

Certification feedback opportunity for the community.