Compare totals for the same partial date range year-over-year in DAX / Power BI

Question

I'm trying to create a table which shows a sum of monthly values for one year compared to the last year's totals (structured as the screenshot below): Monthly Comparison

However, the caveat I'm dealing with is comparing the most current month, which will always contain partial month data (unless it's the last day of the month), to the same date range of the previous year. In the screenshot I attached, our data for January 2018 only goes through January 22nd. However, it's comparing it to the full month of January from 2017, whereas we want that total to be January 1st - 22nd, 2017: Value That Needs to be Updated.

I've tried messing around with various MTD and cumulative totals, but I can't seem to get the logic to work while keeping the aggregation to the monthly level. Any idea what type of logic needs to used in order to compare year-over-year totals, but only do a partial sum for the same date range of a month that is currently in progress?

Thanks in advance.

Can you tell a bit more about the model? What's the structure, is there a date dimension? Futher, what about the other months, are they 'fully' filled with amounts for every day? — TJ_
Sure thing. I'm essentially using two tables for the table visual I showed above: one of them is a source table which contains dates and impression volumes, and the other table is a calendar table. The calendar table is built using the date field from the source table, and then it builds a bunch of other date related fields off of that for things such as the date (last year), month (last year), etc. The table visual is pulling the months from the calendar table, and the impression volumes from the source table. The other months are "fully" filled with amounts as well. — dupton
And to clarify a bit more - I'm using the calendar table for the dates (month and month last year), and using the source table to pull in a sum of impressions, and doing a sum of impressions with a DATEADD function -1 year to bring in the previous year/month's impression volume. — dupton

TJ_ TJ_ · Accepted Answer · 2018-03-21T20:25:38

In my short example, this seems to work:

Total Sales LY 2 = 
VAR MaxDate = EDATE(CALCULATE(MAX(Sales[Date]);ALL(Sales));-12)
RETURN
CALCULATE(
    [Total Sales];
    FILTER(SAMEPERIODLASTYEAR('Date'[Date]);'Date'[Date]<=MaxDate)  
)

I calculate Total Sales for the same period last year, with the max of the last available sales date this year.

Total Sales LY = Comparing last year full month (wrong)
Total Sales LY 2 = Comparing last year month, with max of last sales date

PBIX file

Compare totals for the same partial date range year-over-year in DAX / Power BI

1 Answers