Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago

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.:

 

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)

 

 

 

Any help is much appreciated.  Thank you!

Need Help

3 Replies

  • Ashish_Mathur's avatar
    Ashish_Mathur
    Icon for Super User rankSuper User
    5 years ago

    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.

    • Anonymous's avatar
      Anonymous
      Not applicable
      5 years ago

      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
      • Ashish_Mathur's avatar
        Ashish_Mathur
        Icon for Super User rankSuper User
        5 years ago

        Hi,

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