'DAX formula measures producing same values in pivot table
I have the following data in 1 table in power pivot:
| Folder | Date | Value |
|---|---|---|
| 1 | 1-Nov-2015 | 12 |
| 2 | 1-Nov-2015 | 8 |
| 1 | 2-Nov-2015 | 56 |
| 2 | 2-Nov-2015 | 79 |
| 1 | 3-Nov-2015 | 2 |
| 2 | 3-Nov-2015 | 9 |
I wrote DAX formulas for YTD,MTD,QTD and have done so successfully. For example, the YTD DAX formula is:
MY_YTD = CALCULATE(
SUM(tbl[Value]),
FILTER(ALL(tbl[Date]),
tbl[Date]<=MAX(tbl[Date])
&& YEAR(tbl[Date]) = YEAR(MAX(tbl[Date]))))
However when I try to create a pivot table, placing my created measures MY_YTD, MY_QTD, MY_MTD into the values section, and "Folder" and "Date" in the rows field,I get the same values for all the 3 different measures I have created.
| Folder | Date | MY_MTD | MY_YTD | MY_QTD |
|---|---|---|---|---|
| 1 | 1-Nov-2015 | 12 | 12 | 12 |
| 2-Nov-2015 | 56 | 56 | 56 | |
| 3-Nov-2015 | 2 | 2 | 2 | |
| 2 | 1-Nov-2015 | 8 | 8 | 8 |
| 2-Nov-2015 | 79 | 79 | 79 | |
| 3-Nov-2015 | 9 | 9 | 9 |
I suppose I am missing a minor detail because the pivot table works perfectly fine with only 1 folder but not with multiple folders. Appreciate if anyone can point me in the right direction. Thanks!
edit: adding MY_MTD and MY_QTD as jos woolley suggested.
Just to reiterate: MY_MTD, MY_QTD, MY_YTD gives correct values when there is only 1 folder in the table
MY_MTD = CALCULATE(
SUM(tbl[Value]),
FILTER(ALL(tbl[Date]),
tbl[Date]<=MAX(tbl[Date])
&& MONTH(tbl[Date]) = MONTH(MAX(tbl[Date])
&& YEAR(tbl[Date]) = YEAR(MAX(tbl[Date]))))
MY_QTD = CALCULATE(
SUM(tbl[Value]),
FILTER(ALL(tbl[Date]),
tbl[Date]<=MAX(tbl[Date])
&& ROUNDUP(MONTH(tbl[Date])/3,0) = ROUNDUP(MONTH(MAX(tbl[Date])/3,0)
&& YEAR(tbl[Date]) = YEAR(MAX(tbl[Date]))))
edit2: Found the problem - need to add in tbl[Folder] in ALL() and FILTER()
MY_MTD = CALCULATE(
SUM(tbl[Value]),
FILTER(ALL(tbl[Date],tbl[Folder]),
tbl[Folder] = DISTINCT(tbl[Folder])
&& tbl[Date]<=MAX(tbl[Date])
&& MONTH(tbl[Date]) = MONTH(MAX(tbl[Date])
&& YEAR(tbl[Date]) = YEAR(MAX(tbl[Date]))))
Sources
This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.
Source: Stack Overflow
| Solution | Source |
|---|
