I created a date table and the two formulas below to calculate total volume from the prior month across 4 categories, but when I apply the formula the resulting tables show total volume by each month, not solely the prior month (May). My end goal is to create a line and cluster bar chart where the bars are the prior month volume and the line is the average monthly volume. Appreciate any thoughts or help on what I’m doing wrong.
Date table:
Date =
VAR __dates = CALENDARAUTO()
RETURN
ADDCOLUMNS (
__dates,
"Year", YEAR ( [Date] ),
"Month Number", MONTH ( [Date] ),
"Month Name", FORMAT ( [Date], "MMMM" ), --use MMMM for full month name, January instead of Jan
"Month", FORMAT( [Date], "MMM, YYYY" ), --use MMMMM for full month name, January instead of Ja
"Month Sort", FORMAT( [Date], "YYYY-MM" ),
"Quarter", "Q" & FORMAT( [Date], "Q, YYYY" ),
"Quarter Sort", FORMAT ( [Date], "YYYY-Q" ))
Formula to calculate total volume across all my data categories:
Total Volume = Calculate(Sum('2020 Monthly Production Volume'[Value]))
Formula to calculate total volume across all my data categories:
Sum Prior Month = CALCULATE ([Total Volume], PREVIOUSMONTH('Date'[Date]) )
