Two different measures on the same Powerpivot report column

[kdankwah]

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.

 

[kdankwah]

Disregard the last statement, Correction I want to formulate the last If statement measure to the MTD and the YTD to create the two columns.

Thanks

 

[scottsen]

You will want something like this:

=IF (HASONEVALUE('Combined Years Dataset'[SOURCE]),
      IF (VALUES('Combined Years Dataset'[SOURCE] = "OADPO"),
              [MTD],
              [YTD]
       )
   )

This IF check is to avoid passing multiple values to VALUES() in the grand total cell (which causes blow up). Can also be used to do something different in those total cells

 

[kdankwah]

You will want something like this:

=IF (HASONEVALUE('Combined Years Dataset'[SOURCE]),
      IF (VALUES('Combined Years Dataset'[SOURCE] = "OADPO"),
              [MTD],
              [YTD]
       )
   )

This IF check is to avoid passing multiple values to VALUES() in the grand total cell (which causes blow up). Can also be used to do something different in those total cells

Thank you so much. I appreciate your help. I have thought of doing it sthis way too. I will create two different pivot table reports one for regular and the other for the Purchase Order report. I will then use OLAP cubes to combine and rearrange them. Is my approach OK.

Thanks once again.

 

[scottsen]

Sorry, I don't totally understand your scenario, so it's hard for me to say. I don't know what is in "regular" report and PO report, or what you hope to "combine" about them...