I need an advice on how to do this. I have a Power-pivot summary report that I create every month. 90% of the report row uses this measure for the column MTD:
=CALCULATE (SUM([AMOUNT]),
FILTER (
ALL ( DATE ),
'Date'[YearMonthNumber]= MAX ( 'Date'[YearMonthNumber] )
&& 'Date'[Date] <= MAX ( 'Date'[Date] )
)
)
and the YTD measure is:
=CALCULATE (SUM([AMOUNT]),
FILTER (
ALL ( DATE ),
'Date'[YearNumber] = MAX ( 'Date'[YearNumber] )
&& 'Date'[Date] <= MAX ( 'Date'[Date] )
)
)
This is working perfectly.
The problem is one of the rows in the report will have a different measure, I am using an IF statement below to extract the mtd and ytd figures if the code is OADPO.
=if('Combined Years Dataset'[SOURCE]="OADPO", DatesMTD(date[date]))
I don't know how that will work, its not working anyway. Is this possible in a report?
Can someone please help me formulate all three measures into one measure?
Thanks in advance.
=CALCULATE (SUM([AMOUNT]),
FILTER (
ALL ( DATE ),
'Date'[YearMonthNumber]= MAX ( 'Date'[YearMonthNumber] )
&& 'Date'[Date] <= MAX ( 'Date'[Date] )
)
)
and the YTD measure is:
=CALCULATE (SUM([AMOUNT]),
FILTER (
ALL ( DATE ),
'Date'[YearNumber] = MAX ( 'Date'[YearNumber] )
&& 'Date'[Date] <= MAX ( 'Date'[Date] )
)
)
This is working perfectly.
The problem is one of the rows in the report will have a different measure, I am using an IF statement below to extract the mtd and ytd figures if the code is OADPO.
=if('Combined Years Dataset'[SOURCE]="OADPO", DatesMTD(date[date]))
I don't know how that will work, its not working anyway. Is this possible in a report?
Can someone please help me formulate all three measures into one measure?
Thanks in advance.