比较DAX / Power BI中相同部分日期范围的同比总数 [英] Compare totals for the same partial date range year-over-year in DAX / Power BI

问题描述

我正在尝试创建一个表，该表显示一年的月值与去年的总数之和（结构如下图所示）：

PBIX文件

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.

解决方案

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

这篇关于比较DAX / Power BI中相同部分日期范围的同比总数的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！