CODEWITHSUNDEEP
powerbidaxdate-range
dupton
dupton

Reputation: 15

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

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.

Upvotes: 0

Views: 5016

Answers (1)

TJ_
TJ_

Reputation: 647

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

enter image description here

PBIX file

Upvotes: 3

Related Questions